A little VB help

[Chamberlain]

Here's what I have:


Sub UnhideNonBlankRows()
ActiveSheet.Unprotect
Range("x11").Select
Dim FstRow As Integer
Dim LstRow As Integer

FstRow = "20"
LstRow = "343"
Col = "B"

For i = FstRow To LstRow
If Cells(i, Col) = "2" Then
Cells(i, Col).EntireRow.Hidden = False
End If
Next i
Range("f11").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingRows _
:=False, AllowInsertingHyperlinks:=True, _
AllowSorting:=True, AllowFiltering:=True
End Sub


The problem with this is if you insert rows between the FstRow and LstRow, the row #'s don't change. Basically if I insert a row between 20 and 343, I want the LstRow to become 344. I would imagine that one way to do this would be to make the FstRow and LstRow named ranges (Which I tried), however I'm not good enough with VB to make it work.

Any help would be much appreciated.
Thanks,
Dave

 

[Andrew Poulsom]

Maybe try:

LstRow = ActiveSheet.Cells("A" & ActiveSheet.Rows.Count).End(xlUp).Row

 

[Chamberlain]

That didn't seem to work. It just gave me an error.
Is there a way to name row 343 "Lastrow" or something, so the macro would look something like this theoretically:

FstRow = "20"
LstRow = "Lastrow" (A.K.A. "343")

I don't know if that makes sense or not, but I know that the named range will change is row reference if a row is inserted or deleted.

 

[Andrew Poulsom]

Sorry about the typo there:

LstRow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

 

[Chamberlain]

No need to be sorry. That seems to be working great!

Thanks for you help!
Dave

 

[Chamberlain]

I was wondering if there was a way to make the first row relative as well. I use that macro in a couple of places, one of them I want the first row stationary, but the other one will get rows inserted above the first row. The second is different also in that the distance between the first row and the last row doesn't change.

Thanks,
Dave

 

[Andrew Poulsom]

If the number of rows between the first row and the last row doesn't change you can calculate the first row by subtracting a constant from the last row. Example:

LstRow = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
FstRow = LstRow - 323

 

[Chamberlain]

Thanks for your help.

I'm trying to get better with VB, and I am slowly. I was wondering if you could translate this for me:

ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

Just so I can understand what it's doing.

Thanks again,
Dave

 

[Andrew Poulsom]

That's the equivalent of selecting A65536 [Range("A" & ActiveSheet.Rows.Count)] and pressing End then the up arrow [End(xlUp)] to go to the last cell with an entry in it. The Row property returns the row number of that cell.

 

[Chamberlain]

Okay, I thought I was something like that.
Thanks a lot.
I appreciate your time.