HIDDING ROWS

[ROAST]

HOW DO I USE A MACRO THAT WILL LOOK AT COLUMN "A" IN MY WORK SHEET AND HIDE THE ROWS MARKED "HIDE"?.

[ This Message was edited by: ROAST on 2002-04-02 12:11 ]

[Ricky Morris]

Sub HideRows()
Dim cell As Range
For Each cell In Range("A:A")
If cell.Value = "HIDE" Then
cell.EntireRow.Hidden = True
End If
Next
End Sub

[Jack in the UK]

Hi

Very rare i post VBA buit this is cool, conditional fromat will also work just by adding conditions.. super coll code.. i use this a lot..

One to go arround the world....

////////
Sub Hide_Rows()
On Error GoTo Z
Sheets("Sheet1").Select
Range("A1:A10000").Select 'Jack sets range amend as needed
Selection.Interior.ColorIndex = xlNone
Range("A1").Select
Application.ScreenUpdating = False
Rw = 1
With Sheets("Sheet1") 'Jack in the UK
For Hide1 = _
1 To WorksheetFunction.CountIf(.Columns("A:A"), "HIDE")
Set RHIDEFoundCell = .Columns("A:A").Find(What:="HIDE", _
After:=.Cells(Rw, 1))
RHIDEFoundCell.EntireRow.Hidden = True
Rw = RHIDEFoundCell.Row
Next Hide1
End With
Range("A1").Select
Application.ScreenUpdating = True
Z:
End Sub

//////////////////

[Chris Davison]

conditional format Jack ?

do share mate

[Jack in the UK]

Hi All

I dont post VBA very often either i get ripped appart or someione postes over me, so i dont share but this code i have developed and it super cool. IF you get used to it and work its way, other VBAers will have their method im sure.

BUT.. im a simple south london boys who say @WHY CANT I DO x y Z D F D fvsfjvhsfj all at the same time and bolt on as many as i like, thjis code does this...

OK this is the trick:

........
Sub Hide_Rows() ;
' Jack called this Hide rows as that was the question in real life its called Jacks_Conditions
On Error GoTo Z
' Jack places error handle
Sheets("Sheet1").Select ;
' Jack select sheet can be any one or and in that workbook... COOL!
Range("A1:A10000").Select
'Jack sets range amend as needed (normal Table format so muct be some coumumn...
Selection.Interior.ColorIndex = xlNone
'Jack distroys all the colours.. i use colours a lot IE if in Col A a row says REFER to Chris D then that important so mioght go red in all column... Highlight it//
Range("A1").Select
'Jack selects cell !,1 or A1 same thing
Application.ScreenUpdating = False
' Jacks stops flickering...
Rw = 1 ;
' Jack set Rw to = 1 good place to start again can be any numnber depending of what you want (will always be 1 i add)
With Sheets("Sheet1")
'Jack in the UK select to alter sheet 1 ie with command
For Hide1 = _
1 To WorksheetFunction.CountIf(.Columns("A:A"), "HIDE")
' Jack cheat again uses APPLICATION FUNCTION ie countif.. this FINDS all X i want to find in ONE HIT,, LOOPS.... Yuuuuck..
Set RHIDEFoundCell = .Columns("A:A").Find(What:="HIDE", _
After:=.Cells(Rw, 1)) :
' Jack set look up and what to find ie HIDE in this case
RHIDEFoundCell.EntireRow.Hidden = True ;
' Jack set command to found data IE HIDE it acan be what ever your heart desires.. i use colour entire row a lot///
Rw = RHIDEFoundCell.Row
; Jack sets Rw to whats been found
Next Hide1
' Jack compleats sneaky loop with next command
End With
;Jack compleasts with command
Range("A1").Select
'Jack select A1 or cell(1,1)
Application.ScreenUpdating = True
' Jack set screen updating to true to make changes happen IE if coloured rows makes them show
Z:
' Jack compleats error handle
End Sub
//// OK that a lot for one condition but add in this....

middle section as many as you like and 3 conditions are blown away.. i have had 26 conditions, wonderful does some 60,000 rows in a few seconds fast enough for me.

BE CAREFUL if Hide is types hide SSPACE wond work muste be Hide exactly...

Does that help///