Selecting Range with VBA

scouser22

Board Regular
Joined
Oct 8, 2008
Messages
63
I need to select a range of rows to hide rows with no data in after a macro has imported data.

I have the below

Sub HideRows()

Dim lngLastRow2 As Long

Dim i As Long

With Sheets("Gross Up")
'Find the last row in column B
lngLastRow2 = .Range("B65535").End(xlUp).Row

Range("(lngLastRow2 + 1):415").Select

Selection.EntireRow.Hidden = True
End With
End Sub

The range select does not work (i know i do not have it in the right context), but i can't work out how to put it

anyone help please?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I believe simple is best. Try something like this
Code:
Sub HideRows()
Dim cl As Range
For Each cl In Range("$B$2:$B" & Range("$B$65536").End(xlUp).Row)
If cl = "" Then cl.EntireRow.Hidden = True
Next cl
End Sub
Works on the active sheet
lenze
 
Upvote 0
that would work fine, but is there a way to start it at the bottom row, as i have gaps between the data that i need to keep. it just the gaps below the last peice of data and the total(which will be in a fixed postion each time i use the macro
 
Upvote 0
I've figured it out.

Sub HideRows2()
Dim cl As Range
For Each cl In Range("$B$469:$B" & Range("$B$65536").End(xlUp).Row)
If cl = "" Then cl.EntireRow.Hidden = True
Next cl
End Sub

works a treat

Thanks
 
Upvote 0
Not sure what you want, but maybe
Code:
Range(Cells(lngLastRow2+1,2),Cells(145,2)).EntireRow.Hidden = True
lenze
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top