Last row combined with Current Region VBA

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
493
Office Version
  1. 365
  2. 2019
Platform
  1. 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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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 '
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.....
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top