hide a column

[vikram]

hi

can u help me with this

what i want is that if in a column there is written "Blank" a comman
that hides that particular column

plz help me with thi

 

[Frank Kabel]

Hi
try the following macro (looks in row 1 for this word 'Blank')
Sub hide_columns()
Dim ColNdx As Long
application.screenupdating=False
For ColNdx = 1 to 256
If Cells(1, ColNdx).Value = "Blank" Then
Columns(ColNdx).hidden = True
End If
Next ColNdx
application.screenupdating=True
End Sub

 

[Job]

Hi Vikram,

Here is a similar way.

You didn't specify if it was the first cell in the column, but I'
assuming it is. Try this.

Sub HideIt()
'The sheet needs to be named 'Sheet1', or change the code if it i
something else
For Each col2 In Worksheets("Sheet1").Columns
'This is specifying the value it is looking for..change 'Blank' to b
whatever you wish
If col2.EntireColumn.Cells(1, 1).Value Like "Blank*" Then

col2.EntireColumn.Hidden = True


End If
Next
End Sub


Cheers,

Jo

 

[vikram]

thank u so much

but this will look in row 1 only
can we have it for all rows and a smaller one if it is possible

thank u so much
i really appreciate ur hel

 

[vikram]

hi job!

"Blank" written can be in any column and in any row. and if it is
want to hide that column

thank u so much for ur hel

 

[Frank Kabel]

Hi
o.k. try the following:
Sub hide_columns()
Dim rng as range
dim cell as range
set rng = activesheet.usedrange
application.screenupdating=False
For each cell in rng
If Cell.Value = "Blank" Then
Columns(cell.column).hidden = True
End If
Next cell
application.screenupdating=True
End Sub

 

[Job]

Ok,

Try this.

Sub HideIt()

Selection.CurrentRegion.Select
For Each Cell In Selection
'if you wanted to literally check each cell..I wouldn't recommend as i
would take a long time.
'For Each Cell In ActiveSheet.Cells
If Cell.Value = "Blank" Then
Cell.EntireColumn.Hidden = True

End If
Next Cell

End Su

 

[vikram]

thank u so much but if i want to check only column : A,B,C,D then wha
is the way out

thanks agai

 

[vikram]

but the macro is not working, i have checked it twic

 

[Job]

*but the macro is not working, i have checked it twice *

Are you sure the value in the cell is actually "Blank"? you could us
the 'like' that we used earlier and put Value Like "*Blank*"

Try that..

 

[Frank Kabel]

Hi
try
Sub hide_columns()
Dim Col_index as integer
Dim row_index as long
Dim lastrow as long

application.screenupdating=False
For col_index = 1 to 4
lastrow = ActiveSheet.Cells(Rows.count,
col_index).End(xlUp).row
for row_index = 1 to lastrow
if cells(row_index,col_index).value = "Blank" then
columns(col_index).hidden=True
exit for
end if
next row_index
next col_index
application.screenupdating=True
End Sub

 

[vikram]

i have attached the file, u can check this is a test file

Attachment filename: blank.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=534995

 

[vikram]

any suggestions frnd

 

[Frank Kabel]

Hi
have you tried my macro (see below). Just inserted it in your workbook
and it hides the columns

----
Sub hide_columns()
Dim Col_index as integer
Dim row_index as long
Dim lastrow as long

application.screenupdating=False
For col_index = 1 to 4
lastrow = ActiveSheet.Cells(Rows.count, _
col_index).End(xlUp).row
for row_index = 1 to lastrow
if cells(row_index,col_index).value = "Blank" then
columns(col_index).hidden=True
exit for
end if
next row_index
next col_index
application.screenupdating=True
End Sub

 

[vikram]

thank u so much frank

i am really thankful to u
i really appreciate ur hel