You are here:

Excel/Selecting non-contiguous cells in worksheet using VBA (without hard-coding cell references)

Advertisement


Question
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

Answer
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

About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

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.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.