Last row combined with Current Region VBA

[Tigerexcel]

I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data that has data in all columns so it is possible that the quoted formula may fail if the the last row does not contain data in column D so I may have to find a more foolproof formula that will work in all situations.
The bigger issue that I have is I'd like to somehow link the Lastrow with the CurrentRegion property so that once the last row has been identified I can effectively work on that data range. I've found that something like DataRange = Lastrow.CurrentRegion doesn't work where DataRange is the table data. How can I write this in VBA with a minimum of coding?

 

[footoo]

Also does this coding presuppose that you have already named the range you're looking for as Table2?

Yes, you need a Table named "Table2".

If required, it is possible to loop all Tables without hard coding the names.

 

[Tigerexcel]

Yes, you need a Table named "Table2".

If required, it is possible to loop all Tables without hard coding the names.

Ok thanks for the clarification, the data had been named Table1 and I altered your code accordingly, still getting the same error message.
Also in your rng statement, will the macro be thrown off if the data does not have anything in cell A1?

 

[footoo]

Ok thanks for the clarification, the data had been named Table1 and I altered your code accordingly, still getting the same error message.

You need to Have a Table named "Table2", not a named range.
If you want for a named range :

Sub raneg()
Dim LastRow&, rng As Range
With ActiveSheet.Range("Table3")
    LastRow = .Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Set rng = Range(.Cells(1, 1), Cells(LastRow, Range([A1], .Cells).Columns.Count))
End With
End Sub

Also in your rng statement, will the macro be thrown off if the data does not have anything in cell A1?

No.

 

[Tigerexcel]

Ok thanks for the revision, it works better with the named range. My final question is though if you go to the trouble of making the received data table a named range then the named range will take care of the boundaries of the data table, wouldn't that make finding the last row/column redundant? So you'd simply set your range as Table3 and loop through that etc.

 

[footoo]

Yes, except that you wanted the range only to the last populated row in the Table.

 

[Tigerexcel]

Yes, except that you wanted the range only to the last populated row in the Table.

Ok, thanks for your help.

 

[CephasOz]

As to the first requirement, If you want a foolproof method, have a look at this article - Determining worksheet data area - the function "LastUsedCell" that it contains ensures that all data cells are covered.

 

[footoo]

As to the first requirement, If you want a foolproof method, have a look at this article - Determining worksheet data area - the function "LastUsedCell" that it contains ensures that all data cells are covered.

That doesn't cover what the OP needs.
He was looking for the last used row in a specific range, not the whole sheet.

 

[CephasOz]

Hi @footoo. If a specific range's last row is needed, that's simply:

With rngToUse
    lngLastRow = .Row + .Rows.Count - 1
End With

However, the initial post said:

I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data that has data in all columns so it is possible that the quoted formula may fail if the the last row does not contain data in column D so I may have to find a more foolproof formula that will work in all situations.

And it was to that that I responded.

 

[footoo]

Hi @footoo. If a specific range's last row is needed, that's simply:

With rngToUse
    lngLastRow = .Row + .Rows.Count - 1
End With

However, the initial post said:

I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data that has data in all columns so it is possible that the quoted formula may fail if the the last row does not contain data in column D so I may have to find a more foolproof formula that will work in all situations.

And it was to that that I responded.

I think you need to read the whole thread (including the links in post #2). Just about every way of getting the last row has been covered.

With rngToUse
    lngLastRow = .Row + .Rows.Count - 1
End With

That doesn't locate the last data row.