You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Selecting non-contiguous cells in worksheet using VBA (without hard-coding cell references)

Advertisement

Tom,

I have a worksheet with 2 columns of data. I need to CTRL+select every cell in the first column where values in the second column match some criteria.

Due to the volume of data I would like to do this in VBA. I've been able to write code that identifies the address of each relevent cell. I then write these to an String that ends up looking something like this:

e.g. "'$A$1','$A$5','$A$15',...." etc

What I then need to do is make vba take this string, treat it as a non-contiguous range and select it in the excel workbook as activecells. This is where I'm coming unstuck. Do you have any suggestions?

Thanks!

My Code Attempt:

Sub testSelect()

Dim iRow, lastRow, firstCell As Long

Dim fullSelection As String

Dim fullRange As Range

Dim inputSheet As Worksheet

Set inputSheet = Worksheets("Test1")

lastRow = inputSheet.Cells(Rows.Count, 1).End(xlUp).Row

fullSelection = ""

firstCell = 0

For iRow = 2 To lastRow

If inputSheet.Cells(iRow, 2) = "Area1" Then

If firstCell < 1 Then

fullSelection = fullSelection & inputSheet.Cells(iRow, 1).Address '& "'"

firstCell = 1

Else

fullSelection = fullSelection & "," & inputSheet.Cells(iRow, 1).Address '& "'"

End If

End If

Next iRow

'code to select in workbook

End Sub

Nik,

Here is what you asked for:

Sub testSelect()

Dim iRow, lastRow, firstCell As Long

Dim fullSelection As String

Dim fullRange As Range, r As Range

Dim inputSheet As Worksheet

Set inputSheet = Worksheets("Test1")

lastRow = inputSheet.Cells(Rows.Count, 1).End(xlUp).Row

fullSelection = ""

firstCell = 0

For iRow = 2 To lastRow

If inputSheet.Cells(iRow, 2) = "Area1" Then

If firstCell < 1 Then

fullSelection = fullSelection & inputSheet.Cells(iRow, 1).Address(0, 0) '& "'"

firstCell = 1

Else

fullSelection = fullSelection & "," & inputSheet.Cells(iRow, 1).Address(0, 0) '& "'"

End If

End If

Next iRow

Set r = inputSheet.Range(fullSelection)

Application.Goto r

'code to select in workbook

End Sub

however, if you have a lot of cells, your string can get too big and the procedure will fail. It is better to build a range reference like this

Sub testSelect_Alternate()

Dim iRow, lastRow, firstCell As Long

Dim fullSelection As String

Dim fullRange As Range, r As Range

Dim inputSheet As Worksheet

Set inputSheet = Worksheets("Test1")

lastRow = inputSheet.Cells(Rows.Count, 1).End(xlUp).Row

fullSelection = ""

firstCell = 0

For iRow = 2 To lastRow

If inputSheet.Cells(iRow, 2) = "Area1" Then

If firstCell < 1 Then

Set r = inputSheet.Cells(iRow, 1)

firstCell = 1

Else

Set r = Union(r, inputSheet.Cells(iRow, 1))

End If

End If

Next iRow

If Not r Is Nothing Then

Application.Goto r

Else

MsgBox "No range identified"

End If

'code to select in workbook

End Sub

note that to select, the sheet where you are selecting must be active. Also, there can only be a single cell that is the activecell. Many cells can be selected and one of the cells selected will be the activecell.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Tom, thank you so much. 'Union' was the key. I'd been trying something similar to your alternative suggestion but kept getting errors. Really appreciate your speedy response. |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.