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

[ward376]

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

 

[Mike H]

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

 

[Tom Ogilvy]

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.

 

[Gary''s Student]

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

 

[ward376]

Thanks to all!

ward376