Find Last Row Save Row Number to use as a Constant

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
214
Office Version
  1. 2019
Platform
  1. Windows
I import a large csv file. I know the first column is reliable for counting used rows. Later as columns are added, column A will become column B, C, etc. I need to refer to the last row in probably 20 different modules. and after the first column is inserted, "LastRow" is now in column B, etc.
What I'd like to do is after the csv is imported save the last row as "something", a value in a cell I can reference a named range, so that rather then having to do something similar to the code below everytime I create a new range, put the value of the saved LastRow as the resize to value. Does this make sense? I also set the entire used range as a table if using that is an option.
VBA Code:
Dim LastRow     As Long
    Dim LastCol     As Integer
    Dim tbl         As Range
    Dim ThisWsName  As Range
    Dim ThisWsNow   As Range
    '
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range("A1", Cells(LastRow, LastCol)).Name = "ws_2Users1_Tbl"
    Range("A1").Name = "ws_2Users1_LogOn"
    Range("ws_2Users1_LogOn").Offset(1, 0).Resize(LastRow).Name = "ws_2Users1_RngLogOn"
And after doing that, how do I use it in creating the other new ranges?

TIA

Ron
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Have you tried using something like:
Code:
LR = ActiveSheet.UsedRange.Rows.Count
and saving the result in a cell on your sheet?
Is that what you mean?
 
Upvote 0
Would LR be available as long as the workbook is open? If it is, then LastRow will be too. I'm ignorant if you can keep using it after the module is ended.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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