Select only cells that contain data between 2 columns

Question

I am looking for a method that will select only cells which contain data between TWO columns. I can't wrap my head around the logic I need to accomplish this.

I am thinking that this is my best bet, but am open to other suggestions.

Sheet1.Columns("A3:B1000").SpecialCells(xlCellTypeConstants, 23).Select

With this code I can select the range that contains a value, however it doesn't work simultaneously between the two columns. If column A has data but column B does not, it will still select column A.

Below is what I am looking to do.

Have you considered using a FOR loop with an IF condition to check whether both cells in a particular row have values. If condition is true select both cells. — shash, Feb 22 '18 at 14:21
Why do you want to ***select*** them? Usually [you'd want to avoid](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) `.Select` or `.Selection` — CallumDA, Feb 22 '18 at 14:23
I have considered the FOR loop but I wasn't sure how to retrieve the information and compile it to my use. I am looking to select it right now so I can paste it into another row. Hiding rows, or deleting rows is not an option because I have formulas in adjacent cells I don't want to affect. — Kevin P., Feb 22 '18 at 14:35

Xabier · Accepted Answer · 2018-02-22T14:27:50.053

The following code will do what you expect by filtering any blank cells and then selecting all visible cells, for my example, I used columns A & B, amend this as required.

NOTE: I agree with comments from CallumDA, you would usually want to avoid selecting anything, but yet the example code below will show you how to add that given range to a variable, should you want to do something with it, rather than just select it.

Sub foo()
    Dim ws As Worksheet: Set ws = Sheets("Sheet1")
    'declare and set your worksheet, amend as required
    Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    'get the last row with data on Column A
    Dim rng As Range
    ws.Range("$A$1:$B$" & Lastrow).AutoFilter Field:=1, Criteria1:="<>"
    ws.Range("$A$1:$B$" & Lastrow).AutoFilter Field:=2, Criteria1:="<>"
    Set rng = ws.Range("A2:B" & Lastrow).SpecialCells(xlCellTypeVisible)
    ws.Range("$A$1:$B$" & Lastrow).AutoFilter
    rng.Select
End Sub

Select only cells that contain data between 2 columns

1 Answers1