Last row combined with Current Region VBA

Tigerexcel

Board Regular
Joined
Mar 6, 2020
Messages
209
Office Version
2016
Platform
Windows
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Tigerexcel

Board Regular
Joined
Mar 6, 2020
Messages
209
Office Version
2016
Platform
Windows
Thanks footoo, had a look at these websites but they don't really address my issue, the closest I found was:
Set DataRange = Range("A1:M" & LastRow) but doesn't this assumes that there is data in A1? I receive data that may not have anything in cell A1.
I'm happy with the coding that I mentioned for finding the last row, the bit that I need is to expand the range to wherever the top of the range is.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,936
Office Version
2016
Platform
Windows
The answer is in the links provided.

Examples :
VBA Code:
 LastRow = Range("A:M").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row '
or
VBA Code:
 LastRow = Range("D4:M" & Rows.Count).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row '
 

Tigerexcel

Board Regular
Joined
Mar 6, 2020
Messages
209
Office Version
2016
Platform
Windows
If I was to write a simple macro that changes the font color eg

Sub raneg()
LastRow = Range("A:M").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For Each cell In ?????????
cell.Font.ColorIndex = 5
Next cell
End Sub

What would I type in place of the question marks, assume that the data table extends from B2 to G25 say so that the last row is row 25
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,936
Office Version
2016
Platform
Windows
If I was to write a simple macro that changes the font color eg

Sub raneg()
LastRow = Range("A:M").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For Each cell In ?????????
cell.Font.ColorIndex = 5
Next cell
End Sub

What would I type in place of the question marks, assume that the data table extends from B2 to G25 say so that the last row is row 25
Your code makes no sense - why get the last row in A:M then do a For Each loop that bears no relation to LastRow?

Maybe :
VBA Code:
Sub raneg()
Dim LastRow&
With Range("Table1")
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    For i = 1 To LastRow
        .Columns(1).Cells(i).Font.ColorIndex = 5
    Next
End With
End Sub
Or :
VBA Code:
Sub raneg()
Dim LastRow&, cel As Range
LastRow = Range("B2:G" & Rows.Count).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For Each cel In Range("B2:B" & LastRow)
    cel.Font.ColorIndex = 5
Next
End Sub
 

Tigerexcel

Board Regular
Joined
Mar 6, 2020
Messages
209
Office Version
2016
Platform
Windows
Hi again footoo, I tried the first sample of code with the Table1 reference but the code only formatted a few of the cells in the first column.

Perhaps I'll go back a step. I receive all sorts of data tables of varying lengths, the data doesn't always start in cell A1 and the number of rows can be anything from 10 to several thousand. The data tables will be formatted according to various conventions or templates. I thought that the best way to identify the range would be to identify the last row which is easy enough, then I would somehow select the data table through some form of CurrentRegion property. Perhaps this is the wrong approach.....
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,936
Office Version
2016
Platform
Windows
Hi again footoo, I tried the first sample of code with the Table1 reference but the code only formatted a few of the cells in the first column.
That was the intention of the code - to set the range as the first column of the Table from the first cell down to the first column last data cell.
Perhaps I'll go back a step. I receive all sorts of data tables of varying lengths, the data doesn't always start in cell A1 and the number of rows can be anything from 10 to several thousand. The data tables will be formatted according to various conventions or templates. I thought that the best way to identify the range would be to identify the last row which is easy enough, then I would somehow select the data table through some form of CurrentRegion property. Perhaps this is the wrong approach.....
To set the range from the first cell in the table to the cell which is the intersection of the last column and the last row with data, here's one way :
VBA Code:
Sub raneg()
Dim LastRow&, rng As Range
With ActiveSheet.ListObjects("Table2").DataBodyRange
    LastRow = .Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - .Cells(1, 1).Row + 1
    Set rng = Range(.Cells(1, 1), .Cells(LastRow, .Columns.Count))
End With
End Sub
 

Tigerexcel

Board Regular
Joined
Mar 6, 2020
Messages
209
Office Version
2016
Platform
Windows
That was the intention of the code - to set the range as the first column of the Table from the first cell down to the first column last data cell.

To set the range from the first cell in the table to the cell which is the intersection of the last column and the last row with data, here's one way :
VBA Code:
Sub raneg()
Dim LastRow&, rng As Range
With ActiveSheet.ListObjects("Table2").DataBodyRange
    LastRow = .Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row - .Cells(1, 1).Row + 1
    Set rng = Range(.Cells(1, 1), .Cells(LastRow, .Columns.Count))
End With
End Sub
Hi again again footoo,

I tried the code you specified with both Table1 and Table2 in the With ActiveSheet row but keep getting a "Subscript out of range" error. Also does this coding presuppose that you have already named the range you're looking for as Table2?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,265
Messages
5,443,409
Members
405,234
Latest member
AA90

This Week's Hot Topics

Top