Clear data and hide rows.

[SANTANDER]

I am looking for macro that will remove data from rows with zero value and
hide that rows.
For example, there is 3 columns, some rows contains data like this:

5962-9090801MVA abc CONVERTER I.C. 0.00
5962-9092701M3A 8Bit 35Msps Mil ADC 0.00
5962-9092701MXA 8Bit 35Msps Mil ADC 0.00
5962-9092702M3A 8Bit 35Msps Mil ADC 0.00

I just need search for rows like this, clear this data and hide this rows.
And the same thing for whole worksheet.

thank you.

 

[Gord Dibben]

Sub Clear_Hide_Rows_With_Zero()
FindString = "0"
Set B = Range("C:C").Find(What:=FindString, LookAt:=xlWhole)
While Not (B Is Nothing)
With B.EntireRow
.ClearContents
.Hidden = True
End With
Set B = Range("C:C").Find(What:=FindString, LookAt:=xlWhole)
Wend
End Sub


Gord Dibben MS Excel MVP

 

[SANTANDER]

Thank you, this works.
One note: after hiding multiple rows, the existing data now listed without
sequence, i.e. first rows is 1, then 104, 110, 176 and so on.
Does it possible to list rows with data in sequential order, one for
another, without this empty hidden rows?

Thanks,
Santander

 

[Gord Dibben]

Only by deleting rather than hiding the rows you cleared.

Change .Hidden = True to .Delete


Gord

 

[SANTANDER]

This not work:


Sub Clear_Hide_Rows_With_Zero()
FindString = "0"
Set B = Range("C:C").Find(What:=FindString, LookAt:=xlWhole)
While Not (B Is Nothing)
With B.EntireRow
.ClearContents
.Delete = True
End With
Set B = Range("C:C").Find(What:=FindString, LookAt:=xlWhole)
Wend
End Sub


thanks.
S.

 

[Gord Dibben]

You do not need the = True with .Delete

Were my instructions unclear or ambiguous?

Change .Hidden = True to .Delete


Gord

 

[Gord Dibben]

BTW.................if you are deleting the rows you don't need to ClearContents

Sub Delete_Rows_With_Zero()
FindString = "0"
Set B = Range("C:C").Find(What:=FindString, LookAt:=xlWhole)
While Not (B Is Nothing)
B.EntireRow.Delete
Set B = Range("C:C").Find(What:=FindString, LookAt:=xlWhole)
Wend
End Sub


Gord

 

[SANTANDER]

That's fine, thank you.

Santander

BTW.................if you are deleting the rows you don't need to
ClearContents

Sub Delete_Rows_With_Zero()
FindString = "0"
Set B = Range("C:C").Find(What:=FindString, LookAt:=xlWhole)
While Not (B Is Nothing)
B.EntireRow.Delete
Set B = Range("C:C").Find(What:=FindString, LookAt:=xlWhole)
Wend
End Sub


Gord