Help with Excel and Visual Basic

MartMax · 01-08-2007 1:51pm #1

Hi,

I have little knowledge with Visual Basic but trying myself learning by trial and error.

I have a table in an excel spreadsheet say with two columns, A and B. Just say rows in column A contain project name and B is the number of staff. Input in B rows is basically fed from other sheet.

What I am trying to achieve is to automatically hide all rows with 0 no of staff. I have used the following codes and works, however I I wish I could avoid using the first example which is painful if I have to deal with hundreds of rows.

Rows("1499:1499").Hidden = Range("$B1499") = 0
Rows("1500:1500").Hidden = Range("$B1500") = 0
Rows("1501:1501").Hidden = Range("$B1501") = 0
Rows("1502:1502").Hidden = Range("$B1502") = 0
and so on...

The next script however return with an error - mistype.

        Rows("1499:1550").Hidden = Range("$B1499:$B1550") = 0

I tried inserting .Value in the second example but still no luck. Where did I do wrong? Any suggestions and comments are always welcome.

Thanks!

Marty

nude_hamster · 01-08-2007 4:17pm

i dont know VB, but there should be something like a for loop or a while loop in VB

something like the code

A=0
B=0
while(notAtEndOfPage){
.
..
Rows("A,B").....
A = A+1
B = B+1
..
.
}

FSL · 01-08-2007 8:46pm

You can loop through each row in a spreadsheet, check the appropriate Cell(s) against the required criteria and the perform the desired action(s).
If you look in the help in Excel you will see many examples of looping through rows and checking specific cells.

EL_Loco · 02-08-2007 4:30pm


Sub HideRowsIfZero()
'

Dim lr1 As Integer
Dim r As Integer

' find the amount of rows used
With ActiveSheet.UsedRange
    lr1 = .Rows.Count
End With

' go through all the rows used
For r = 1 To lr1

' the number 3 refers to column C, change this number if
' you want to check a different column. A=1 B=2 and so on
    If (ActiveSheet.Cells(r, 3) = 0) Then
        Rows(r).Select
        Selection.EntireRow.Hidden = True
    End If
Next r

End Sub

MartMax · 02-08-2007 5:31pm

Cheers lads.

I have also managed to do the following for same results after couple of cut and paste from various codes

Sub Hide_Blank_Row

'Have a start row
x = 1499

'Loop until blank row is found
Do While Cells(x, 5).Value <> ""

'Hide Cell with Zero Value in Column 5 (E)
Rows(x).Hidden = Cells(x, 5) = 0

'Loop to next row
x = x + 1
Loop

End Sub

Thanks again.

Marty

cyberbob · 03-08-2007 7:16pm

Autofilter is possibly a neater way ... depends on yer scenario

Range(Cells(w, x), Cells(w, z)).Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=XX, Criteria1:="<>0", Operator:=xlAnd

MartMax · 03-08-2007 10:09pm

i already have autofilter ran on the same sheet so the code is used to in summary at the bottom of the sheet, where most of the input comes from hundreds of line from the top.

cheers,
marty

Help with Excel and Visual Basic

Comments