+ Reply to Thread
Results 1 to 17 of 17

Macro that deletes rows below a certain value

Hybrid View

Biased Historian Macro that deletes rows below... 01-14-2011, 11:50 AM
Domski Re: Macro that deletes rows... 01-14-2011, 11:57 AM
Biased Historian Re: Macro that deletes rows... 01-14-2011, 01:21 PM
royUK Re: Macro that deletes rows... 01-14-2011, 01:28 PM
Biased Historian Re: Macro that deletes rows... 01-14-2011, 02:28 PM
Domski Re: Macro that deletes rows... 01-14-2011, 02:07 PM
Biased Historian Re: Macro that deletes rows... 01-14-2011, 02:34 PM
Biased Historian Re: Macro that deletes rows... 01-14-2011, 10:54 PM
Biased Historian Re: Macro that deletes rows... 01-14-2011, 11:02 PM
Biased Historian Re: Macro that deletes rows... 01-15-2011, 01:46 PM
Biased Historian Re: Macro that deletes rows... 01-15-2011, 03:25 PM
broro183 Re: Macro that deletes rows... 01-15-2011, 04:33 PM
broro183 Re: Macro that deletes rows... 01-15-2011, 07:28 PM
Biased Historian Re: Macro that deletes rows... 01-15-2011, 07:49 PM
broro183 Re: Macro that deletes rows... 01-15-2011, 09:04 PM
Biased Historian Re: Macro that deletes rows... 01-15-2011, 09:24 PM
broro183 Re: Macro that deletes rows... 01-15-2011, 09:45 PM
  1. #1
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Macro that deletes rows below a certain value

    Hello,

    I am looking for help in creating a macro that will delete the 42 rows below a cell and the cell itself that contain the following value; (any number) of (any number)



    For example a cell containing ; 3 of 18, would be deleted along with the 42 cells (rows) below that. All of the data is in column A.

    Any help would be greatly appreciated.

    Thanks!

    D
    Last edited by Biased Historian; 01-15-2011 at 09:26 PM.

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Macro that deletes rows below a certain value

    Not tested but maybe:

    Sub test()
    
    Dim lngLoopRow As Long
    
    For lngLoopRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    
        If Range("A" & lngLoopRow) = "3 of 18" Then
        
            Range("A" * lngLoopRow).Resize(43, 1).EntireRow.Delete
            
        End If
        
    Next lngLoopRow
    
    End Sub

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Macro that deletes rows below a certain value

    It looks like the right idea but "3 of 18" would be only good for page 3 of page 18. I need to delete based of of any page of any page, so any number "of" any number...

    Thanks for the help so far...


    D

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Macro that deletes rows below a certain value

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Macro that deletes rows below a certain value

    The workbook is exactly as described- beginning cell is any number combo three digits long max (123) of any number combo three digits long max (123), the last cell to be deleted to DATE.

    The code provided above looks great but I am wondering if it is possible in VB to recognize any three digit number "of" any three digit number in a cell as the execution point?






    D
    Attached Files Attached Files

  6. #6
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Macro that deletes rows below a certain value

    Will there be any other entries in column A that follow the format '? of ?'

    Dom

  7. #7
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Macro that deletes rows below a certain value

    Will there be any other entries in column A that follow the format '? of ?'

    Dom
    No that is the only time- it happens at each page break in a report

  8. #8
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Macro that deletes rows below a certain value

    I just re-read your question and I want to correct my response- yes it appears at every page break in the report, which is contained in column A....so it wil go 1 of such and such.....a few rows down from there will be 2 of such and such, and so on...

  9. #9
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Macro that deletes rows below a certain value

    Using the suggested code above, would it work if instead of "3 of 18", it said "1 of(wildcard)", 2 of(wildcard)", 3 of (wildcard)...this way it will not matter what the total number of pages are? If so, how would it look for the first few pages and I will build out the rest?

  10. #10
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Macro that deletes rows below a certain value

    BUMP-

    Looking for help on how the provided code can be modified to delete not just for "3 of 18", but also "1-100 of*", meaning 1 of, 2 of, 3 of, 4 of...and so on. I would want a wild card after "of" so that it will not matter what the total number of pages are in the report.

    Sub test()
    
    Dim lngLoopRow As Long
    
    For lngLoopRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    
        If Range("A" & lngLoopRow) = "3 of 18" Then
        
            Range("A" * lngLoopRow).Resize(43, 1).EntireRow.Delete
            
        End If
        
    Next lngLoopRow
    
    End Sub

  11. #11
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Macro that deletes rows below a certain value

    Also I am getting an error 13, mismatch when I try the code provided above.

    This part turns yellow on the error;

    Range("A" * lngLoopRow).Resize(43, 1).EntireRow.Delete

  12. #12
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Macro that deletes rows below a certain value

    hi Biased Historian

    Quote Originally Posted by Biased Historian View Post
    Range("A" * lngLoopRow).Resize(43, 1).EntireRow.Delete
    Try changing the "*" for an "&".

    I'll have a play & post something else in a while...


    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  13. #13
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Macro that deletes rows below a certain value

    hi Biased Historian,

    Here's a slightly different approach which worked on my test data - if I understand your needs correctly...
    I haven't used this approach before, would you mind letting me know how fast it runs compared to Dom's version on your real data?

    option explicit
    Sub test2()
    Const pbStr As String = " of "    'page break string
    Const NumOfRwsToDel As Long = 43
    Const FirstRow As Long = 2
    Dim LastRow As Long
    Dim i As Long    'reused loop index
    Dim rngArr As Variant
    Dim ws As Worksheet
    Dim pbRowColl As Collection    'page break row collection
    Dim curStr As String    'current string
    Dim LocOfpbStr As Long    'location of page break string
    
        Set ws = ActiveSheet
        Set pbRowColl = New Collection
    
        'create an "in memory" variant array of the info in column A
        With ws
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            rngArr = .Range(.Cells(FirstRow, "a"), .Cells(LastRow, "a"))
        End With
    
    
        For i = LBound(rngArr) To UBound(rngArr)
            curStr = rngArr(i, 1)
            LocOfpbStr = InStr(1, curStr, pbStr)
            If LocOfpbStr Then
                If IsNumeric(Left(curStr, LocOfpbStr - 1)) And IsNumeric(Right(curStr, Len(curStr) - (LocOfpbStr + Len(pbStr) - 1))) Then
                    With pbRowColl
                        .Add Item:=FirstRow - LBound(rngArr) + i
                    End With
                End If
            End If
        Next i
    
        'loop through the page break row collection backwards & select ('delete) the desired rows
        For i = pbRowColl.Count To 1 Step -1
            With ws
                .Cells(pbRowColl.Item(i), "a").Resize(NumOfRwsToDel, .Columns.Count).Select    'Delete
            End With
        Next i
    
        'For lngLoopRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        '    If Range("A" & lngLoopRow) = "3 of 18" Then
        '        Range("A" * lngLoopRow).Resize(43, 1).EntireRow.Delete
        '    End If
        'Next lngLoopRow
    
        Set ws = Nothing
        Set pbRowColl = Nothing
    End Sub
    hth
    Rob

  14. #14
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Re: Macro that deletes rows below a certain value

    Hi...I tried your code. It highlights the cell "2 of 5" and the exact cells to be deleted below that cell, however it does not actually delete the selected rows or continue to 3 of 5...

    I changed that character change from the earlier code per your suggestion and it works great when I change the value to "2 of 5" or "3 of 5", however I do not know how to make it target multiple values.

    Thank you for your efforts thus far...

  15. #15
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Macro that deletes rows below a certain value

    hi,

    Deleting rows changes the Row Number of any remaining rows that are below where the deleted rows previously existed (ie they all get renumbered), therefore row deletions are best to do from the bottom of your data up to the top. This is why both Dom & my code includes the statement "step -1" in the first line of the Loop (so it goes "backwards" or "upwards").

    In my code you can change:
    .Cells(pbRowColl.Item(i), "a").Resize(NumOfRwsToDel, .Columns.Count).Select    'Delete
    to
    .Cells(pbRowColl.Item(i), "a").Resize(NumOfRwsToDel, .Columns.Count).Delete
    and everything should get deleted.

    hth
    Rob

  16. #16
    Forum Contributor
    Join Date
    06-29-2010
    Location
    Boston, Massachusetts
    MS-Off Ver
    Excel 2010
    Posts
    111

    Wink Re: Macro that deletes rows below a certain value

    Rob,

    Thank you very much, swapping those two lines did the trick! Even better my theory that eliminating the data signifying page breaks on a report would address an issue from a more complex formatting code appears to be spot on.

    Dom thank you for your efforts! I tipped the scale towards both of you.

    I ran your code first, then a larger one by waterserv, and now items that were being dropped on page breaks are being accounted for.

    Thanks again!!!


    Dave
    Last edited by Biased Historian; 01-15-2011 at 09:26 PM.

  17. #17
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Macro that deletes rows below a certain value

    hi Dave,

    I'm pleased we could help - Thanks for adding to our rep

    I haven't tested it, but here is a slight change to Dom's code which may work...

    option explicit
    Sub modified_test()
    Dim lngLoopRow As Long
    Dim NumOfPBs As Long    'Total number of page breaks.
    Dim curPBNum As Long    'current page break number
    
        'ask the user...
        NumOfPBs = Application.InputBox("Please enter the total number of pages that are in this report:", "How many pages...?", Type:=1)
    
        If NumOfPBs > 2 Then
            'set the intial value of curPBNum to equal the last value on the report
            curPBNum = NumOfPBs
            'loop from bottom to top
            For lngLoopRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
                If Range("A" & lngLoopRow) = curPBNum & " of " & NumOfPBs Then
                    Range("A" * lngLoopRow).Resize(43, 1).EntireRow.Delete
                    'decrease the page number for the next iteration of the loop
                    curPBNum = curPBNum - 1
                End If
            Next lngLoopRow
        End If
    End Sub
    hth
    Rob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1