ExcelBanter - Clear data and hide rows.

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Clear data and hide rows. (https://www.excelbanter.com/excel-programming/407904-clear-data-hide-rows.html)

SANTANDER

Clear data and hide rows.

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

Clear data and hide rows.

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

On Tue, 18 Mar 2008 19:14:42 +0200, "SANTANDER"
wrote:

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.

SANTANDER

Clear data and hide rows.

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" <gorddibbATshawDOTca wrote in message
...
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

On Tue, 18 Mar 2008 19:14:42 +0200, "SANTANDER"
wrote:

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

Clear data and hide rows.

Only by deleting rather than hiding the rows you cleared.

Change .Hidden = True to .Delete

Gord

On Tue, 18 Mar 2008 22:24:26 +0200, "SANTANDER"
wrote:

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" <gorddibbATshawDOTca wrote in message
.. .
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

On Tue, 18 Mar 2008 19:14:42 +0200, "SANTANDER"
wrote:

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.

SANTANDER

Clear data and hide rows.

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" <gorddibbATshawDOTca wrote in message
...
Only by deleting rather than hiding the rows you cleared.

Change .Hidden = True to .Delete

Gord

On Tue, 18 Mar 2008 22:24:26 +0200, "SANTANDER"
wrote:

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" <gorddibbATshawDOTca wrote in message
. ..
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

On Tue, 18 Mar 2008 19:14:42 +0200, "SANTANDER"

wrote:

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

Clear data and hide rows.

You do not need the = True with .Delete

Were my instructions unclear or ambiguous?

Change .Hidden = True to .Delete

Gord

On Wed, 19 Mar 2008 20:41:08 +0200, "SANTANDER"
wrote:

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" <gorddibbATshawDOTca wrote in message
.. .
Only by deleting rather than hiding the rows you cleared.

Change .Hidden = True to .Delete

Gord

On Tue, 18 Mar 2008 22:24:26 +0200, "SANTANDER"
wrote:

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" <gorddibbATshawDOTca wrote in message
...
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

On Tue, 18 Mar 2008 19:14:42 +0200, "SANTANDER"

wrote:

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

Clear data and hide rows.

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

On Wed, 19 Mar 2008 13:47:21 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

You do not need the = True with .Delete

Were my instructions unclear or ambiguous?

Change .Hidden = True to .Delete

Gord

On Wed, 19 Mar 2008 20:41:08 +0200, "SANTANDER"
wrote:

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" <gorddibbATshawDOTca wrote in message
. ..
Only by deleting rather than hiding the rows you cleared.

Change .Hidden = True to .Delete

Gord

On Tue, 18 Mar 2008 22:24:26 +0200, "SANTANDER"
wrote:

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" <gorddibbATshawDOTca wrote in message
m...
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

On Tue, 18 Mar 2008 19:14:42 +0200, "SANTANDER"

wrote:

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.

SANTANDER

Clear data and hide rows.

That's fine, thank you.

Santander

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Wed, 19 Mar 2008 13:47:21 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

You do not need the = True with .Delete

Were my instructions unclear or ambiguous?

Change .Hidden = True to .Delete

Gord

On Wed, 19 Mar 2008 20:41:08 +0200, "SANTANDER"
wrote:

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" <gorddibbATshawDOTca wrote in message
...
Only by deleting rather than hiding the rows you cleared.

Change .Hidden = True to .Delete

Gord

On Tue, 18 Mar 2008 22:24:26 +0200, "SANTANDER"

wrote:

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" <gorddibbATshawDOTca wrote in message
om...
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

On Tue, 18 Mar 2008 19:14:42 +0200, "SANTANDER"

wrote:

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.

All times are GMT +1. The time now is 12:40 PM.