Loop through column(s) to check values, perform action based on check
 

I hope someone has something like this worked out - How to loop
through a column and if every cell contains a certain value (i.e.all
zeros), hide or delete the column.

Thanks!
ward376

Loop through column(s) to check values, perform action based on ch
 

Hi,

This looks at the used range in column A and hides the row if the cell value
is equal to XXXXX

Sub stantial()
lastrow = Range("A65536").End(xlUp).Row
For x = lastrow To 1 Step -1
If Cells(x, 1).Value = "XXXXX" Then 'change to suit
Rows(x).EntireRow.Hidden = True
End If
Next
End Sub


Mike
"ward376" wrote:

I hope someone has something like this worked out - How to loop
through a column and if every cell contains a certain value (i.e.all
zeros), hide or delete the column.

Thanks!
ward376

Loop through column(s) to check values, perform action based on ch
 

Sub checkcolumns()
Dim rng as Range, r as Range, cell as range
set rng = Range("A1:Z1")
for each cell in rng
set r = Range(cell,Cells(rows.count,cell.column).End(xlup) )
if application.countif(r,cell) = Application.CountA(r) then
cell.EntireColumn.Hidden = True
else
cell.EntireColumn.Hidden = False
end if
Next
End Sub

Assumes the first row will contain values.

--
regards,
Tom Ogilvy


"ward376" wrote:

I hope someone has something like this worked out - How to loop
through a column and if every cell contains a certain value (i.e.all
zeros), hide or delete the column.

Thanks!
ward376

Loop through column(s) to check values, perform action based on ch
 

Let's say A1 thru G10 looks like:

0 0 0 0 0 0 0
1 0 0 0 0 0 0
1 1 0 0 1 0 0
0 1 0 0 1 0 0
1 0 1 0 1 0 1
0 1 0 0 1 0 0
1 1 0 0 1 0 0
0 0 0 0 1 0 1
1 0 1 0 0 0 1
1 1 1 0 0 0 0


and we want to hide cols like D & F which are all zeros

Try this:

Sub sistance()
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nLastColumn = r.Columns.Count + r.Column - 1
For i = 1 To nLastColumn
hide_it = True
For j = 1 To nLastRow
If Cells(j, i).Value < 0 Then
hide_it = False
End If
Next
If hide_it Then
Columns(i).EntireColumn.Hidden = True
End If
Next
End Sub
--
Gary''s Student - gsnu200753


"ward376" wrote:

I hope someone has something like this worked out - How to loop
through a column and if every cell contains a certain value (i.e.all
zeros), hide or delete the column.

Thanks!
ward376

Loop through column(s) to check values, perform action based on ch
 

Thanks to all!

ward376