VBA Copy last filled row from B to AV

[Forberi]

Hello,

I´m looking for a way to copy the last filled row in an excel sheet using VBA.

The problem is:

I only want to copy from B to AV. So it have to find the last filled row, skip the first cell (filled with "Overall:") and copy the rest.



Does anyone of you know a way to do that?

PS: this is an automatic generated file. The last row is a different one evertime so I cant simply copy the area "B35:AV35" for example.

Regards,
Forb

 

[Jake Blackmore]

The best way I have discovered of doing this is to SET a dynamic Named Range (Used in your excelworksheet) in your VBA and use that to manipulate code with a constantly changing range. Do you know how to set a dynamic named range?

 

[Forberi]

Hello Jake,

thank you for your answer!

I´ve never used dynamic named ranges bevore. And for the first time google wasn´t that helpful.

Could you explain it to me, how to use it in my context?

BR

 

[Forberi]

Can anyone else explain this to me?

 

[MARK858]

Try the code below, obviously you need to change the select to copy in your actual code.

Sub xxx()
Dim LstRw As Long
LstRw = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Range(Cells(LstRw, "B"), Cells(LstRw, "AV")).Select
End Sub

 

[Jake Blackmore]

Hi - I thought I would quickly run through a dynamic named range.

Basically you are setting a name range that moves according to the data - this means that the Data you test needs to be assessed as either being Numeric or Text.

If it was numeric it would look to match a big number - it is unlikely to find this exceptionally large number and when it finishes trying it would have reached the bottom of the data set and we can use that to determin the last row. The same happens when we use text. If you were unsure which was bigger than you could have a simple IF statement picking which is bigger.

If the data was just in Column A and it was just numeric then it would be:
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(10^308,Sheet1!$A:$A,1),1)
Notice I go from A1 but this can be changed.

If the data was just in Column A and it was just text then it would be:
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A,1),1)

If you wanted the range to extend to column AV but you were going to assess the data in column B to determin the range and the data in column B was numeric which I think your data is then it would be:
=Sheet1!$B$1:INDEX(Sheet1!$AV:$AV,MATCH(10^308,Sheet1!$B:$B,1),1)

I thought I would also mention if you don't know how many columns you have but it starts in column B and you have header text then you could have:
=Sheet1!$B$1:INDEX(Sheet1!$B:$XFD,MATCH(10^308,Sheet1!$B:$B,1),MATCH(REPT("z",255),Sheet1!$1:$1,1)-1)
Note - I had to -1 at the end as we started the range in Column B not column A so the column number is off by 1.

If you then wanted to use this range in VBA you need to DEFINE it, SET it then you can USE it. So your code would be something like:

Option Explicit
Public Sub CopyDynamicRange()
Dim drngNew As Excel.Range
    Set drngNew = Sheet1.Range("drng_DynamicNamedRange")
    Call drngNew.Copy(Destination:=Sheet2.Range("A1"))
    Application.CutCopyMode = False
End Sub