Results 1 to 14 of 14

Thread: More VBA Help

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    More VBA Help

    RG (or anyone): maybe you can solve this macro one for me, too.

    On one sheet in a workbook, I have a set of rows and 2 columns (e.g., B23:C28). On another sheet in the same workbook I have the values in from B23:B28 in columns A2:A7.

    I want a macro that first makes the rows A2:A7 BLANK if there's an "x" in C23:C28, then sorts A2:B7 by column A (no heading).

    Is that easy in VB?

    In addition, I want to create a new sheet in workbook2 that is named using the concatenation of the values in workbook1, cells B4 and Q2 . This VBA is killing me. I obviously need a class. Phew.

  2. #2
    New Lounger
    Join Date
    Aug 2010
    Location
    New Zealand
    Posts
    13
    Thanks
    11
    Thanked 1 Time in 1 Post
    Do you have a sample workbook to work with?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Here's a sample of what I have in the real application. (I'll also want to change the "x" to blank)
    Attached Files Attached Files
    Last edited by kweaver; 2012-07-03 at 09:36.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    This should do what you are after if I understand correctly. Please note that since the values on the Incidents sheet are references to those on the Hold sheet when the rows on the Hold sheet are cleared it will show 0 where the rows were cleared on the Hold sheet.
    Code:
    Option Explicit
    
    Sub DeleteRows()
    
       Dim lCurRow As Long
       
       lCurRow = 2
       
       Do
       
         If WorksheetFunction.VLookup(Cells(lCurRow, 1), Range("Incidents!DeleteTable"), 2, False) = "x" Then
           Debug.Print "Clear: " & Format(lCurRow)
           Cells(lCurRow, 1).EntireRow.ClearContents
         End If
         
         lCurRow = lCurRow + 1
        
       Loop Until Cells(lCurRow, 1).Value = ""
       
       ' Get it sorted!
       
           Cells(Rows.Count, 2).End(xlUp).Select
        
        Range("A1:" & ActiveCell.Address(, xlA1)).Select
        With ActiveWorkbook.Worksheets("Hold").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:A4"), _
             SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:B4")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    End Sub
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Got an error.

    Clip0001.jpg

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    Did you copy the code into your file or use the file I attached? If you copied the code you have to save your file as an .xlsm {macro enabled} file.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I used the attachment.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    Don't know what the problem is as it works fine on my machine. Here's another copy just in case.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Got the same error again. I don't need to select anything before running the macro, do I? I didn't. Just ran the macro.

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    No you don't need to select anything. Sorry but I made an assumption I shouldn't have. I assumed you would run the macro from the Hold sheet but the copy I sent you opens on the Incidents sheet and if run from there the error will happen. I've corrected the macro to select the Hold sheet so you shouldn't encounter any further problems.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks. Will try in a sec. The "hold" sheet will actually be hidden from the user. Do I need to modify the code to unhide, do it's thing, then rehide?

    And, if you don't mind one other... once the deleting and sorting is completed, can the "x" marks be removed, too?

    Thanks,

    Kevin
    Last edited by kweaver; 2012-07-03 at 14:20.

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    Yes it does need to be Visible for the macro to work.
    Code:
    Option Explicit
    
    Sub DeleteRows()
    
       Dim lCurRow As Long
       
       lCurRow = 2
       With Worksheets("Hold")
          .Visible = True
          .Select
       End With
       
       Do
       
         If WorksheetFunction.VLookup(Cells(lCurRow, 1), Range("Incidents!DeleteTable"), 2, False) = "x" Then
           Cells(lCurRow, 1).EntireRow.ClearContents
         End If
         
         lCurRow = lCurRow + 1
        
       Loop Until Cells(lCurRow, 1).Value = ""
       
       ' Get it sorted!
       
           Cells(Rows.Count, 2).End(xlUp).Select
        
        Range("A1:" & ActiveCell.Address(, xlA1)).Select
        With ActiveWorkbook.Worksheets("Hold").Sort
            .SortFields.Clear
            .SortFields.Add Key:=Range("A2:A4"), _
             SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Range("A1:B4")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Sheets("Hold").Visible = False
    
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    That did the trick. Thanks again.

    And, if you don't mind one other... once the deleting and sorting is completed, can the "x" marks be removed, too?

    I've tried a number of non-working attempts inside the IF but none work for me (of course).

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RetiredGeek: Are you suffering with the heat wave or surviving OK?

    [And, if you don't mind one other... once the deleting and sorting is completed, can the "x" marks be removed, too?

    I've tried a number of non-working attempts inside the IF but none work for me (of course). ]

Posting Permissions

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