Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro to hide row if cell=0 3

Status
Not open for further replies.

Allilue

Technical User
Joined
Sep 14, 2000
Messages
189
Location
GB
Hello,

Is there someone who can help me with a spreadsheet I have? I have about a million rows, but most of them are zeros. I need to keep them there but is there a macro that can hide the row if a cell within a specific column = zero? I'm hoping this will be very simple!

Thanks!
Allison
 
No need for a macro, just filter the data to show those rows where the column value doesn't equal zero.
 
Here you go:
Code:
Sub HideRows()
Dim StartRow As Integer, EndRow As Integer
Dim TargetCol As String, x As Integer
StartRow = 3
EndRow = 10000
TargetCol = "C"
For x = StartRow To EndRow
   If Range(TargetCol & x).Value = "0" Then
      Range(TargetCol & x).EntireRow.Hidden = True
   End If
Next x
End Sub
Just set StartRow, EndRow, and TargetCol to whatever you want.

Let me know if that does what you're after!

VBAjedi [swords]
 
I have about a million rows
Which application ? Can't be Excel.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Thanks! I forgot about that!! :o)
 
Awww, c'mon PH! It's called hyperbole! Honestly. . .

[lol]

I sure hope you were indulging in some very dry sarcasm.

VBAjedi [swords]
 
I have similar problem. Using the existing VBA, posted by VBAjedi, can this VBA be changed to accommodate my need?

1. Can you post the code how to hide the rows, if the targeted column is "blank", instead of zero?

2. Can you post the code how to hide the rows, if the targeted column is either "BLANK" or "ZERO"?

Thank you.
 
Replace this:
If Range(TargetCol & x).Value = "0" Then
By this:
If Range(TargetCol & x).Value = "0" _
Or Trim(Range(TargetCol & x).Value) = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
PHV,

Thank you for your help. I have another question.

Let's say, I have 2 Target Columns, instead of one. I tried to modify the code with:

TargetCol = "C" or "D" -> However, this code did not work.

Do you have any suggestions or it will be a totally different giant code?

Thank you again.

 
You may consider using 2 variables or harcode like this:
If Range("C" & x).Value = "0" _
Or Trim(Range("C" & x).Value) = "" _
Or Range("D" & x).Value = "0" _
Or Trim(Range("D" & x).Value) = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 

The best solution for this is the one Molby suggested in the first response - use FILTERING.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
 
Hi PHV,

Look like it hided the rows more than I would like it to be.

This is the modified codes:

******************************

Sub HideRows()

Dim StartRow As Integer, EndRow As Integer
Dim TargetCol As String, x As Integer
StartRow = 3
EndRow = 500
'TargetCol = "C"
For x = StartRow To EndRow
If Range("C" & x).Value = "0" _
Or Trim(Range("C" & x).Value) = "" _
Or Range("D" & x).Value = "0" _
Or Trim(Range("D" & x).Value) = "" Then
Range("C" & x).EntireRow.Hidden = True
Rance("D" & x).EntireRow.Hidden = True
'Range(TargetCol & x).EntireRow.Hidden = True
End If
Next x
End Sub

******************

I omiited the TargetCol.

Can you check whether the codes are correct? I think one of the line is not correct. Any suggestions? Thanks again.

 
You want (C or D) or (C and D) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
We are looking for (C or D), NOT (c and D).

If one of these C or D column, shows zero or blank, the result is to hide that row.

Thank you.
 
Remove either one of the 2 "EntireRow.Hidden = True" lines.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top