Hide Blank rows

Originally Posted by Charity

I have to make a list of people who will be getting charity baskets from our organization

I first list people who are nominated on sheet 1
on sheet 2 I mark with an "X" in column A if they will not be getting a basket

Sheet 3 picks up anyone who does not have an X in column A
The rows with in "X" on sheet 2 will be blank on sheet 3 (actually have a formula putting "" into any cell with an "X" in column A of sheet 2

On sheet 4 I want those blank rows to be hidden

I know, I probably have unnecessary sheets on this spreadsheet, but I want each step to be documented as to what occured.

Is there anyway to do this?

I will be making mailing labels from the list---either I hide the blank rows, or get quite a few blank mailing labels after I merge

thank you for your help

Hi,

To hide rows, select Tools, Macros, Record new macro, select any cell, then Stop Recording.
Tools Macro, Macros, select the macro and Edit.

Replace what is there with

Please Login or Register  to view this content.

Save your work, then run the macro.

hth
---

Originally Posted by iturnrocks

Heres how I do it, because I dont know macros yet.

iturnrocks, I hope that you never get more than 255 consecutive zeros (or that the data isn't more than 1 column wide)


---

Originally Posted by iturnrocks

I have- when that happens I drop down to row 1000 or start a new sheet. But now that I see the macro, I will attempt to learn somthing new. I imagine my 32mb spreadsheet will decrease in size once I figure it out.

Hopefully, yes.

I have used your method, but more to join consecutive rows than to remove blanks, and generally to about 150 max.
The macro is easy, just put the code into a Module sheet, (edit a macro gives you that) and you can assign a letter to run it if required.

---

Originally Posted by Charity

I tried to enter the following code as suggested

Sub Macro1()
' Hide Rows
Dim iRow As Long
Dim iLastRow As Integer
With ActiveSheet
iLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
For iRow = iLastRow To 1 Step -1
If .Cells(iRow, 1) = "" Then
.Rows(iRow).EntireRow.Hidden = True
End If
Next
End With
End Sub

I keep getting

RunTime Error 1004
Application-defined or object-defined error

with the red part highlighted

Hi,

Does the sheet you are currently selected to have entries in column A?

---

Originally Posted by iturnrocks

Brian,

The thing im currently using it for is to recall what raw materials are in a finished product.

I have over 500 raw materials, and I have a sheet with finished goods horizontal on row 1 and raw materials listed vertical in column A. Then numbers in the corresponding row.

Example: finished good fg101 is composed of 1 part 1001, 2 parts 1005, 1-1010....

Please Login or Register  to view this content.

Then in another sheet I have an input where I can type fg101 and it will list all the raw materials minus the spaces. That way you dont have to scroll past all the spaces to see whats in the fg.

Can you set up the macro to run whenever a part number is entered into the cell?

iturnrocks,

as Charity is having a problem with the question on this thread can you transfer your qyuuestion to a new thread

Thanks
---

Originally Posted by Charity

I tried to enter the following code as suggested

Sub Macro1()
' Hide Rows
Dim iRow As Long
Dim iLastRow As Integer
With ActiveSheet
iLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
For iRow = iLastRow To 1 Step -1
If .Cells(iRow, 1) = "" Then
.Rows(iRow).EntireRow.Hidden = True
End If
Next
End With
End Sub

I keep getting

RunTime Error 1004
Application-defined or object-defined error

with the red part highlighted

Charity,

Does the sheet you are currently using have entries in column A?

the line indicated is a 'stock standard' line used in many places.

---

Originally Posted by Charity

It worked like a charm

I redid it on THE CORRECT PAGE and it worked

Wonderful!

Can you explain what each line of the macro does?

I like to learn from the help I get on a forum. If I understand better, I can do more on my own

Thank you again

Hi,

Good to see, and thanks for the response.

iLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
For iRow = iLastRow To 1 Step -1
If .Cells(iRow, 1) = "" Then
.Rows(iRow).EntireRow.Hidden = True

finds the last row with data in column A
Loops from that row backwards up the worksheet
tests for "" in A
and Hides the row.

Hope this helps.
---

Hi,

Originally Posted by Charity

just to understand better


The rows that stayed, had an "X" in them

Yes, was that not what was wanted?
Your original of "The rows with in "X" on sheet 2 will be blank on sheet 3 (actually have a formula putting "" into any cell with an "X" in column A of sheet 2" was not entirely clear, but further you said "I will be making mailing labels from the list---either I hide the blank rows, or get quite a few blank mailing labels after I merge" which seemed to indicate 'hide blanks'

What it hid was rows that were blank (I had deleted the "X")

but the code says to hide those with "" (an empty string)

you can reverse the test to Not = "X" if required, but blank, deleted and empty should all have been hidden.

and what confounds me is something like "End(xlUp).Row"

finds the last used item in the column from 65536 working 'Up'

and "Cells(iRow, 1)"

'Cells' are standard units of reference in Excel, the intersection of Rows and Columns.

Hope this is all clear now.
---