How to get last row variable to update

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
43
I'm still trying to learn VBA so I hope someone can help me please. I have some rows of data that I need to clean up. I set a variable, lr = Cells(Rows.Count, "A").End(xlUp).Row, to determine how many rows are in my data set based on column A. First I want to delete any blank rows and then I want to filter for certain criteria and delete more rows. Since the total number of rows keeps changing, here is my dilemma: How do I get my variable (lr) to update to the new number of rows? My work around has been to declare a new variable, such as lrow, every time my data set changes. I know there must be a better way of doing this but I don't know what I don't know. I would appreciate any help and guidance someone could offer. Thank you.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
Depending on your actual code, you can probably just reset the lr variable.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
Exactly the same way you give it a value initially.
 

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
43

ADVERTISEMENT

So it's as simple as repeating the same line of code, the DIM statement and the lr = Cells(Rows.Count, "A").End(xlUp).Row code? Wow. Does this work for most or all variables?
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
Not Dim part, you declare it once, although a Dim inside a loop is ok.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You just need to repeat this part
VBA Code:
lr = Cells(Rows.Count, "A").End(xlUp).Row
 
Solution

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
396
Office Version
  1. 365
Platform
  1. Windows
Why make Excel reassign it multiple times to the same data type? The memory usage is miniscule but is unnecessary.

I wasn't very explicit, so that's on me. What I tried to mean was it won't throw an error in that case.
 

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
43
I tried just restating the variable again and it worked. I had no idea to solutions was this simple. Thank you Fluff and thank you Gokhan Aycan for the guidance and solution.
 

Forum statistics

Threads
1,144,162
Messages
5,722,842
Members
422,460
Latest member
VBA_Noob01

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