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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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.
 

Forum statistics

Threads
1,140,938
Messages
5,703,271
Members
421,289
Latest member
fbohlandt

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