A little VB help

Chamberlain

Board Regular
Joined
Jan 6, 2006
Messages
149
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Chamberlain

Board Regular
Joined
Jan 6, 2006
Messages
149
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry about the typo there: :oops:

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

Chamberlain

Board Regular
Joined
Jan 6, 2006
Messages
149

ADVERTISEMENT

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

Thanks for you help!
Dave
 

Chamberlain

Board Regular
Joined
Jan 6, 2006
Messages
149
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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:

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

Chamberlain

Board Regular
Joined
Jan 6, 2006
Messages
149
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,859
Messages
5,544,710
Members
410,631
Latest member
JFORTH97
Top