Results 1 to 4 of 4

Excel 2010 VBA - How to - select multiple cells, then select .entirerow for each cell.

This is a discussion on Excel 2010 VBA - How to - select multiple cells, then select .entirerow for each cell. within the Excel Questions forums, part of the Question Forums category; What I am trying to do is tidy up an Excel report by deleting all the unwanted rows of data. ...

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Location
    Norway
    Posts
    50

    Default Excel 2010 VBA - How to - select multiple cells, then select .entirerow for each cell.

    What I am trying to do is tidy up an Excel report by deleting all the unwanted rows of data.

    Code:
    Range("1:1").Find(what:="MCCR*resp*", lookat:=xlWhole).Activate
    Range(ActiveCell.Offset(1, 0).Address, ActiveCell.End(xlDown).Address).Select
    For Each cell In Selection
        If cell <> "ASPF" Then
            firstAddress = cell.Address
            Rng = Rng & cell.Address & ","
            End If
    Next cell
    If Rng <> "" Then
            Rng = Left(Rng, Len(Rng) - 1)
            Range(Rng).Select
            Selection.EntireRow.Delete
            End If
    The Rng is dimmed as String.

    What I tried to do is first .find the column and define the range of the selection I want to work with.

    Then I run a For...Next loop to identify all the cells with a value other than "ASPF", and add the address of those cells into a string called Rng so all the addresses are listed out as $G$3, $G$4, $G$7,$G$11 and so on.

    All of this seems to work.

    However, when I attempt to .Select all the cells defined in the Rng, I get an error message.

    What I wanted to do was .select all the cells with the unwanted data, then simply expand the selection by using .EntireRow and then .Delete all of the rows leaving me with only the data I need to work with.

    The error message I receive is: "Run-time error '1004': Method 'Range' of object '_Global' failed"

    Unfortunately I'm not too sharp on Excel code (yet) but I have a suspicion that the Rng which is dimmed as a String gets overflowed with data (since the report has a total of 8700 rows and likely around 5000 of those should be deleted) or that the Rng somehow can't handle the amount of addresses being fed into it from the For...Next.

    Anyone know what trips up my code, or how/what to use instead to get it working?

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Livorno, Italy
    Posts
    1,950

    Default Re: Excel 2010 VBA - How to - select multiple cells, then select .entirerow for each cell.

    If you want delete rows in a range you have to start from the last row, because when you delete a row the range is not longer the same.
    "for each" is not the good way, but

    lastrow = ......
    for j= lastrow to first step -1
    .........
    next
    Last edited by patel45; Aug 27th, 2012 at 11:56 AM.

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,988

    Default Re: Excel 2010 VBA - How to - select multiple cells, then select .entirerow for each cell.

    Try this.
    Code:
    Dim cell As Range
    Dim rng As String
    Dim arrAddresses
    Dim I As Long
    
        Range("1:1").Find(what:="MCCR*resp*", lookat:=xlWhole).Activate
    
    
        For Each cell In Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column).End(xlUp))
    
            If cell <> "ASPF" Then
    
                rng = rng & cell.Address & ","
            End If
        Next cell
    
        If rng <> "" Then
    
            rng = Left(rng, Len(rng) - 1)
            arrAddresses = Split(rng, ",")
    
            For I = UBound(arrAddresses) To LBound(arrAddresses) Step -1
    
                Range(arrAddresses(I)).EntireRow.Delete
            Next I
        End If
    If posting code please use code tags.

  4. #4
    Board Regular
    Join Date
    Oct 2011
    Location
    Norway
    Posts
    50

    Default Re: Excel 2010 VBA - How to - select multiple cells, then select .entirerow for each cell.

    Quote Originally Posted by Norie View Post
    Try this.
    Code:
    Dim cell As Range
    Dim rng As String
    Dim arrAddresses
    Dim I As Long
    
        Range("1:1").Find(what:="MCCR*resp*", lookat:=xlWhole).Activate
    
    
        For Each cell In Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column).End(xlUp))
    
            If cell <> "ASPF" Then
    
                rng = rng & cell.Address & ","
            End If
        Next cell
    
        If rng <> "" Then
    
            rng = Left(rng, Len(rng) - 1)
            arrAddresses = Split(rng, ",")
    
            For I = UBound(arrAddresses) To LBound(arrAddresses) Step -1
    
                Range(arrAddresses(I)).EntireRow.Delete
            Next I
        End If
    Thanks Norie, this code worked brilliantly and left me with the data I needed.

    Since I'm a shameless "google for the things I need then copy/paste code and adjust it" type of newbie programmer I am always eager to learn exactly what the different parts of the code actually do.

    I'll run through with how I think it's working and I'd really appreciate it if you could correct me if I'm mistaken (only way to learn, aside from classes and courses).

    rng = Left(rng, Len(rng) - 1) ... 'this simply reduces the length of the string by 1, thus removing the comma separator at the end of the string

    arrAddresses = Split(rng, ",") ... 'this populates the arrAddresses array and puts one range value (cell) into each element.

    For I = UBound(arrAddresses) To LBound(arrAddresses) Step -1 ... 'now it gets a bit technical for me, but I think this is telling the For...Next to use the Upperbound (highest 'active' element in the array) to the Lowerbound (lowest 'active' element in the array), and work through that list/range backwards by stepping it in increments of -1 (thus starting at bottom of the list in Excel). A question in that regard though, does Ubound = first/top element, i.e. arrAddresses(1), or does it mean the highest number, e.g. arrAddresses(1500)?

    Range(arrAddresses(I)).EntireRow.Delete ... 'using this while inside the For...Next will remove the rows one at a time from bottom to top, according to what value I has, based on the Step defined in the code after For (line I listed above).

    Hopefully I understood this correctly. Loving how VBA makes my days at work easier (when it's working) so I'm eagerly 'sponging' in any knowledge I can

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com