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???
Solved: Hide rows
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???
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.
[VBA]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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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!!!
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
So basically, all I need to add is the following in my Macro???
Range(Cells(12:212)).SpecialCells(xlCellTypeBlanks).RowHeight = 0
[VBA]Range("A12:A212").SpecialCells(xlCellTypeBlanks).RowHeight = 0[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Doesn't work...
Gives me an error message when I launch the macro and reach the line mentionned above!!!
Can you post your workbook?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Here is what I have in my macro:
[VBA]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[/VBA]
Add some error handling in case there are no blanks
[vba]On Error Resume Next
Range("A12:A212").SpecialCells(xlCellTypeBlanks).RowHeight = 0
On Error GoTo 0
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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???
Refer to post #8
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Can't post my document... Too big and it also include many confidential company informations!!! Sorry!!!
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...
Posting Permissions
Reply With Quote