Select Non-Contiguous Excel Rows?

[RH610]

Is there a way, in VBA, to select a number of non-contiguous Excel rows based upon the content of the cell in column A; for instance highlight all the rows in which cell A contains the letter D?

Thank You

 

[SkipVought]

Hi,

What are you going to do with this selection?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RH610]

Delete the rows.

Thank You

 

[SkipVought]

Well then here's what I would suggest.

Start at the BOTTOM of your range with the data you are going to test and incriment your way up.

As you encounter data that meets your delete criteria, delete the row. Navigating from the top down will not work reliably doing row deletes.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RH610]

OK. Thanks. I was hoping to do it as I would manually. That is, highlight all the rows to be deleted (manually I would hold the control key down and select the rows) and then do one "delete" (manually, I would press Edit and Delete). If not doable in VBA I will do the way you suggested.

Thanks Again

 

[SkipVought]

Well there are ALL kinds of ways to do things in VBA. Why don't you turn on the macro record function and then make your selections and delete and see how that looks. It will not be useable as is, but it will give you an idea maybe.

We ALL do this from time to time

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[bryanbayfield]

Lots of ways of doing this in VBA, this is perhaps the most flexible (although probably not the fastest).

ActiveSheet.UsedRange
Set rngDel = [a1]
ActiveSheet.[a1].AutoFilter Field:=1, Criteria1:="d"
For Each rw In ActiveSheet.UsedRange.Rows
    If rw.Hidden = False And rw.Row > 1 Then
        Select Case rngDel
            Case [a1]
                Set rngDel = rw
            Case Else
                Set rngDel = Union(rngDel, rw)
            End Select
        End If
    Next
ActiveSheet.Columns(1).AutoFilter
rngDel.EntireRow.Delete

Play around with the filter/ macro recorder to get the coding you need.