Find Last Row Save Row Number to use as a Constant

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
153
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,994
Office Version
  1. 365
  2. 2010
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?
 

Shiseiji

Board Regular
Joined
Oct 23, 2009
Messages
153
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,270
Messages
5,657,753
Members
418,411
Latest member
Excellency

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