Auto Hide / Unhide blank rows

[henrym]

Hello everyone,

I am new to excel VBA and hoping someone can help with a problem i have run into. My worksheet called "Data" has a cell that changes (A5) projects and based on that change I used index functions to pull multiple rows of data from another sheet. I have set up basically a range of rows (A5:U20) that these will fit in, but the problem is some projects have more rows than others, and I would like to automatically hide/unhide rows based on if they are blank or not. If more info is needed, let me know. Thanks

 

[rpaulson]

try this

Sub do_it()

Rows("5:20").Hidden = False
For Each cell In Range("A5:A20").Cells
If cell = "" Then Rows(cell.Row).Hidden = True
Next

End Sub

Ross

 

[henrym]

Thanks for the reply,

When I put that code in, it just hides all the rows (5:20) regardless of what is in them.

 

[henrym]

If this helps at all, here is a picture of my spreadsheet. Basically, its a drop down menu where it says "Select Project" and I will be changing that and comparing different projects. There will be data from columns E-T depending on the project, and basically no data in columns A and E. I had also changed your code accordingly since I moved some stuff around since i first posted it.

Here's a link if the picture didn't load

(Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting)

 

[rpaulson]

If there is data in column A then that row will not hide.

 

[henrym]

Awesome, I got it working. Now, at this point I assigned it to a button, but is there any way that it could just be automatic? And again, thanks for your time, this is super useful for me.

 

[Err]

> Now, at this point I assigned it to a button,

Have you thought about using Autofilter on the column?

 

[rpaulson]

to get it to auto run when you change cell A5
Right click on sheet name - view code - then paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A5")) Is Nothing Then Do_it
End Sub

 

[henrym]

@Err, yeah. The workbook had previously used an autofilter, and it was just clunky and didn't work too well with the data

@rpaulson you're a genius. thank you so much for the help, it all works perfectly.