Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through column(s) to check values, perform action based on ch
Thanks to all! ward376 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check or uncheck a check box based on a cell value | Excel Discussion (Misc queries) | |||
Check if all values in a column is identical | Excel Programming | |||
How to check for duplicate values within a column | Excel Programming | |||
Macro to perform action based on value in cell | Excel Programming | |||
check for insert row action... | Excel Programming |