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
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--