PDA

View Full Version : Solved: Hide rows



sb003848
08-05-2009, 10:52 AM
I would like to run a Macro that will search rows 12 to 111 and 112 to 212 and when cell "A" is empty, hide the row.

How can that be done???

mdmackillop
08-05-2009, 11:06 AM
I don't understand the two ranges. Give this a try. The first lines are to increase the UsedRange size, as SpecialCells appears not to work outwith it.
Sub HideRows()
Cells(Rows.Count, 1) = "x"
Cells(Rows.Count, 1).ClearContents
Range(Cells(12, 1), Cells(111, 1)).SpecialCells(xlCellTypeBlanks).RowHeight = 0
End Sub

sb003848
08-05-2009, 11:09 AM
Sorry about the 2 ranges...

Let's say I want to hide rows in which cell "A" is empty (between row 12 to 212), what would the macro be like???

Thanks!!!

mdmackillop
08-05-2009, 11:26 AM
Change 111 to 212 in my previous code. If you have data after row 212, you can delete the first 2 lines of the routine.

sb003848
08-05-2009, 11:38 AM
So basically, all I need to add is the following in my Macro???

Range(Cells(12:212)).SpecialCells(xlCellTypeBlanks).RowHeight = 0

mdmackillop
08-05-2009, 11:41 AM
Range("A12:A212").SpecialCells(xlCellTypeBlanks).RowHeight = 0

sb003848
08-05-2009, 12:03 PM
Doesn't work...

Gives me an error message when I launch the macro and reach the line mentionned above!!!

mdmackillop
08-05-2009, 12:19 PM
Can you post your workbook?

sb003848
08-05-2009, 12:23 PM
Here is what I have in my macro:

Windows("Fall - 2009 (30’s).xls").Activate
Sheets("OTT-OCT").Select
Range(“A12:A212”).RowHeight = 12,75
Windows("recap - Fall - 2009 (30's).xls").Activate
Sheets("OTT-OCT").Select
Range("A12:I212").Select
Selection.Copy
Windows("Fall - 2009 (30’s).xls").Activate
Sheets("OTT-OCT").Select
Range("A12").Select
ActiveSheet.Paste
Windows("recap - Fall - 2009 (30's).xls").Activate
Sheets("OTT-OCT").Select
Range("P12:T212").Select
Selection.Copy
Windows("Fall - 2009 (30’s).xls").Activate
Sheets("OTT-OCT").Select
Range("P12").Select
Sheets("OTT-OCT").Select
ActiveSheet.Paste
Range(“A12:A212”).SpecialCells(x1CellTypeBlanks).RowHeight = 0
Range("A1").Select
Windows("recap - Fall - 2009 (30's).xls").Activate
Windows("Fall - 2009 (30’s).xls").Activate
Sheets("Tally FALL 2009").Select

mdmackillop
08-05-2009, 12:32 PM
Add some error handling in case there are no blanks


On Error Resume Next

Range("A12:A212").SpecialCells(xlCellTypeBlanks).RowHeight = 0
On Error GoTo 0

sb003848
08-05-2009, 12:46 PM
Nope!!! Nothing... But why can't I just add something in my macro that will hide any rows between rows 12 and 212 where cell "A" is blank??? Like for example (I know it's not like that but anyways):

Range(A12:A212).select
If a = empty
then hide.row

You see what I mean???

mdmackillop
08-05-2009, 12:51 PM
Refer to post #8

sb003848
08-05-2009, 12:52 PM
Can't post my document... Too big and it also include many confidential company informations!!! Sorry!!!

sb003848
08-05-2009, 01:48 PM
Did a bit of digging on the net and found this:

Range(“A12:A212”).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Hidden = True

This works great...