Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Help with Excel and Visual Basic

Options
  • 01-08-2007 1:51pm
    #1
    MartMax
    Registered Users Posts: 1,667 ✭✭✭


    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


Comments

  • Options
    #2
    nude_hamster
    Closed Accounts Posts: 161 ✭✭nude_hamster


    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
    ..
    .
    }


  • Options
    #3
    FSL
    Registered Users Posts: 1,456 ✭✭✭FSL


    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.


  • Options
    #4
    EL_Loco
    Registered Users Posts: 901 ✭✭✭EL_Loco 


    
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


  • Options
    #5
    MartMax
    Registered Users Posts: 1,667 ✭✭✭MartMax


    Cheers lads.

    I have also managed to do the following for same results after couple of cut and paste from various codes :D
    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


  • Options
    #6
    cyberbob
    Closed Accounts Posts: 82 ✭✭cyberbob


    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


  • Advertisement
  • Options
    #7
    MartMax
    Registered Users Posts: 1,667 ✭✭✭MartMax


    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


Advertisement