• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Variables assignments outside of codes

JEHalm

New Member
Sorry, I'm sure there is a better way to Title this. I'd like to use variables outside of this code so I do not have to change them all the time. I assume that I need to declare to variables dim C and dim D perhaps. How do I assign the value.


Dim y As Long

For y = Range("U1:U" & Range("U" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(y, 21).Value < 0 Or Cells(y, 21).Value > 10000 Then Cells(y, 1).EntireRow.delete Shift:=xlUp


Next y


Dim c As Long

Dim d As Long


c = Range("ba10000")

d = Range("ba210000")


Dim y As Long

For y = Range("U1:U" & Range("U" & Rows.Count).End(xlUp).Row).Rows.Count To 2 Step -1

If Cells(y, 21).Value < c Or Cells(y, 21).Value > d Then Cells(y, 1).EntireRow.delete Shift:=xlUp


Next y


Am I screwing up with the variable references?? How can I get it fixed


Also, do I need to worry about deleting rows/columns that would move the variable refernces at ba10000 and ba10001?


Jdubs
 
Jdubs


You do need to worry about referencing cells below where you could possibly delete

If you delete a row above 10000, then c = Range("ba10000") will still reference 10000, VBA code doesn't update references automatically.

A better way is to define Range("ba10000") as a Named Formula, in the Name Manager

Then use

c = Range("myName")

This will update automatically as the reference is controlled outside VBA
 
Back
Top