VBA Save Last Row and Last Column Value or Address as String in Named Range for use in many Modules

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
168
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
I import a large csv file from a query I wrote. I know column A is reliable for finding the last row, Later I have to insert columns on the left so a typical LastRow has to be changed as what was once "A" is now "C", etc.
Rather than have to modify the next module reference for the lastrow every time a column is inserted, is there a smart way to both save and later use the last row/last column address as a string, or the count, in a range that can later be used for creating ranges in inserted columns to the left of "A" ?
Is it possible to simply use an offset from the range ws_2Users_LogOn to re-create my LastRow in each module?
Is a row address/column address is the smart way to do this, or if there is a way to use the saved values in the example as part of an R1C1 address.?

Am I making sense?

So this is what I know how to do,
I know how to capture the row and column count as a value in a range.
I do not know how to use that as a reference in a formula as part of R1C1
I do not know how to capture the last row as a "A#" or R1C1 string
I do not know how to then use the new range as a reference like LastRow is used inside a single module.

TIA to anyone who can mentor me through what is and isn't smart/possible or if I just need to stick with last row/last column in each module and keep up with where my reference column is.

Ron

VBA Code:
Mysheet.Activate
Dim LastRow                       As Long
Dim LastCol                         As Integer
Dim UsersLastRow               As Range
Dim UsersLastCol                As Range
With MySheet
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Range("A1", Cells(LastCol)).Name = "ws_2Users_SearchRow"
Range("UsersLastRow").Value = Cells(Cells.Rows.Count, "A").End(xlUp).Row 'range is on a hidden worksheet
Range("UsersLastCol").Value = Cells(1, Columns.Count).End(xlToLeft).Column 'range is on a hidden worksheet
Range("A1").Name = "ws_2Users_LogOn"
Range("ws_2Users_LogOn").Offset(1, 0).Resize(LastRow).Name = "ws_2Users_RngLogOn"
Range("ws_2Users_RngLogOn").NumberFormat = "@"

'--more code--
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
Different ways without saving anything; on a smart worksheet easy via the UsedRange property for example …​
 

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
168
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Different ways without saving anything; on a smart worksheet easy via the UsedRange property for example …​
For anyone who may see this in a search, I believe Marc is suggesting the code below. As a validation, the value can be captured:
VBA Code:
Dim LastRow  As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("TestLastCell").Value = LastRow 'where "TestLastCell" is a named range on a worksheet used for validating/testing.
However there are some "it depends".

Ron de Bruin has pointed out some pitfalls with this UsedRange and xlCellTypeLastCell, namely if you make any changes and then want to exclude those rows/columns from your count you must delete the row/columns and then [Save] the workbook and re-run the code.
I will typically rerun the
VBA Code:
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
if I have made row changes which doesn't require saving the workbook first.

When talking about the "Last Row" it helps to be sure everyone has the same understand of what is meant. Form a Stack Overflow posting:
  1. Used = non-blank, i.e., having data.
  2. Used = "... in use, meaning the section that contains data or formatting." As per official documentation, this is the criterion used by Excel at the time of saving. See also this official documentation. If one is not aware of this, the criterion may produce unexpected results, but it may also be intentionally exploited (less often, surely), e.g., to highlight or print specific regions, which may eventually have no data. And, of course, it is desirable as a criterion for the range to use when saving a workbook, lest losing part of one's work.
  3. Used = "... in use, meaning the section that contains data or formatting" or conditional formatting. Same as 2., but also including cells that are the target for any Conditional Formatting rule.

None of the above include formulas and I'm not knowledgeable enough to know if they fit in 2 & 3 or if a 4th case is necessary.
Since I'm working with a csv import and a column known to contain no empty or null cells, #1 is normally what I'm using.
Regardless, all of this is finding the last row in every module.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
If you are looking for the last row that is displaying a value, you can use this...
Code:
LastUsedRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
If there are cells with formulas that display the empty text string ("") after the last displayed value, those cells will be ignored. If you need the last row which either contains a constant or a formula even if that last row contains a formula displaying the empty text string (""), you can use this...
Code:
LastUsedRow = Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row
Both of the above formulas ignore cells with formatting. Since these formulas are so "simple", you can use them to calculate the last row within your code whenever you want.
 

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
168
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks. The last row when the column is known. So the answer is to always know what column to use as it will shift when columns are added on the left. Amazing at times the complex things Excel will do, but not what would at first thought would be simple.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I am not sure if you have understood my formulas correctly... you do not need to know the column containing the last row with them as they will figure out where the last row is no matter what column it is in.
 
Solution

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
168
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
I am not sure if you have understood my formulas correctly... you do not need to know the column containing the last row with them as they will figure out where the last row is no matter what column it is in.
You're right. I didn't understand, thank you. Knowing your talent and knowledge, I have no doubt it will work. I will have to give it some study to understand why it works.
Thanks so much!

Ron
 

Forum statistics

Threads
1,148,182
Messages
5,745,210
Members
423,933
Latest member
ankushmukherjee

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
Top