Can anyone help me out with an Excel problem?





Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2007
    Location
    Durban
    Age
    49
    Posts
    356
    Thanked: 0

    Default Can anyone help me out with an Excel problem?

    I've got a workbook with eight worksheets in it, named sheet1 - sheet8.

    I'm running a macro to hide rows with zero values, which works across all eight of the worksheets.

    I've added a ninth worksheet, named "Orders", which I want to exclude from this macro, but can't seem to get it right.

    Does anyone know what I should add/change in order to do this?

    Sub HideRowsWithZero()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
    sh.Select
    Dim x As Integer
    Dim CheckValue
    x = 1
    Range("A1").Activate
    Do Until x > ActiveSheet.UsedRange.Rows.Count
    CheckValue = ActiveCell.Offset(0, 3).Value
    If CheckValue = 0 Then
    ActiveCell.EntireRow.Hidden = True
    ElseIf CheckValue <> 0 And ActiveCell.EntireRow.Hidden = True Then
    ActiveCell.EntireRow.Hidden = False
    End If
    ActiveCell.Offset(1, 0).Activate
    x = x + 1
    Loop
    Range("A1").Activate
    Next sh
    End Sub

  2. #2
    Join Date
    May 2009
    Location
    Hillcrest, Malaysia
    Age
    63
    Posts
    18,855
    Thanked: 6460

    Default

    Quote Originally Posted by SteveB View Post
    I've got a workbook with eight worksheets in it, named sheet1 - sheet8.

    I'm running a macro to hide rows with zero values, which works across all eight of the worksheets.

    I've added a ninth worksheet, named "Orders", which I want to exclude from this macro, but can't seem to get it right.

    Does anyone know what I should add/change in order to do this?
    Look at the stemant in blue below.

    Sub HideRowsWithZero()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
    sh.Select
    Dim x As Integer
    Dim CheckValue
    x = 1
    Range("A1").Activate
    Do Until x > ActiveSheet.UsedRange.Rows.Count
    CheckValue = ActiveCell.Offset(0, 3).Value
    If CheckValue = 0 Then
    ActiveCell.EntireRow.Hidden = True
    ElseIf CheckValue <> 0 And ActiveCell.EntireRow.Hidden = True Then
    ActiveCell.EntireRow.Hidden = False
    End If
    ActiveCell.Offset(1, 0).Activate
    x = x + 1
    Loop
    Range("A1").Activate
    Next sh
    End Sub

    Try using an absolute value as apposed to an expression for the X = test. So maybe change it to Do Until X=8 or something similar.

    Keith
    Cheers

    ZS5KAD
    3 V8's
    NA TwinTurbo SuperCharger
    A V6 and an inline 4

    The frogs are starting to notice that the water is getting warm but it is already too hot to do anything about it.....

  3. #3
    Join Date
    May 2011
    Location
    Gordon's Bay
    Age
    41
    Posts
    29
    Thanked: 0

    Default

    This might be what you were looking for:

    Sub HideRowsWithZero()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> "sheet9" Then
    sh.Select
    Dim x As Integer
    Dim CheckValue
    x = 1
    Range("A1").Activate
    Do Until x > ActiveSheet.UsedRange.Rows.Count
    CheckValue = ActiveCell.Offset(0, 3).Value
    If CheckValue = 0 Then
    ActiveCell.EntireRow.Hidden = True
    ElseIf CheckValue <> 0 And ActiveCell.EntireRow.Hidden = True Then
    ActiveCell.EntireRow.Hidden = False
    End If
    ActiveCell.Offset(1, 0).Activate
    x = x + 1
    Loop
    Range("A1").Activate
    End if
    Next sh
    End Sub

  4. #4
    Join Date
    May 2011
    Location
    Gordon's Bay
    Age
    41
    Posts
    29
    Thanked: 0

    Default

    Forgot to add:
    Remember that VBA is case sensitive, so your sheet name should reflect the case, which could be "Sheet9" or "sheet9", depending on how you entered the name.

  5. #5
    Join Date
    Jan 2009
    Location
    Oral
    Age
    62
    Posts
    1,870
    Thanked: 338

    Default

    Fluffy, the line you refer to counts the rows, not the sheets, the line which should be changed is:
    For Each sh In ActiveWorkbook.Worksheets

    Should be changed to For sheets 1 to sheets 8 (not sure of the syntax).

    Steve, the easiest way to solve your problem is to insert a column in sheet 9 and fill it with numbers, that way every row has a numer and none will be hidden

    C
    If you ain't livin on the edge, you're taking up too much space!

  6. #6
    Join Date
    Aug 2008
    Location
    Rangeview Krugersdorp
    Age
    70
    Posts
    6,464
    Thanked: 1242

    Default

    Can't you just crumple them up and buy a pad of foolscap?
    There is no task too simple for some people to complicate !



    Ford Figo 2016

  7. #7
    Join Date
    May 2008
    Location
    Alberton
    Age
    57
    Posts
    1,508
    Thanked: 23

    Default

    My 5c worth
    This works, and is a little quicker as it does not have to keep moving the active cell.
    If the worksheets are really long iv'e added the line to stop the screen updating during the running of the macro to speed things up a bit too.


    Sub HideRowsWithZero()
    Dim sh As Worksheet
    Dim x As Integer
    Dim CheckValue
    Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> "sheet9" Then
    sh.Select
    Range("A1").Activate
    For x = 1 To ActiveSheet.UsedRange.Rows.Count + 1
    CheckValue = ActiveCell.Offset(x - 1, 3).Value
    If CheckValue = 0 Then
    ActiveCell.Offset(x - 1, 3).EntireRow.Hidden = True
    ElseIf ActiveCell.Offset(x - 1, 3).EntireRow.Hidden = True Then
    ActiveCell.Offset(x - 1, 3).EntireRow.Hidden = False
    End If
    Next
    Range("A1").Activate
    End If
    Next sh
    Application.ScreenUpdating = True

    End Sub

Similar Threads

  1. WJ 2.7 CRD Maintenance
    By wtrlan in forum Jeep
    Replies: 240
    Last Post: 2018/04/19, 07:20 AM
  2. Super Select Display problem
    By arnijr in forum Mitsubishi
    Replies: 4
    Last Post: 2009/09/18, 11:10 AM
  3. Toyota Hilux D4D 3.0L D/C 4x2- Handeling Problem
    By NicStix in forum General 4x4 Discussion
    Replies: 2
    Last Post: 2009/07/29, 12:53 PM
  4. Old Prado electric drive-lock problem
    By beemer in forum Toyota
    Replies: 2
    Last Post: 2008/10/23, 05:26 PM
  5. Steering Problem
    By Hugo Lap in forum Isuzu
    Replies: 6
    Last Post: 2008/09/15, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •