Select Alternating Columns With Unknown End Point

Okay, so I have been googling to figure out how to write a macro that will select every other column on my workbook. All that keeps coming up is conditional formatting, which I do not want. I am not trying to highlight the columns in alternating colors, I just want to select them.

I am starting at column F and I would like it to search until the end of the columns and select every other column ending with the last column with data in it.

I know I could search by a specific row and tell it to find the first blank cell then offset it, but there is no particular row that will always have data in it, so I guess it needs to check by column until it finds the first column without any data in all rows and then offset it.

Anyone know what I am talking about here?

I know it should be fairly simple but I couldn't seem to find any answers anywhere else.

Also, anyone know any good websites that have definitions for some basic macros? I'd love to start learning how to write this stuff on my own.

Thanks.

-Amy Kate

Hello Amy Kate,

This macro will start with column "F" (6) and select alternating columns up to the Last Column with a heading in row 1. The end of each column is found automatically and the columns are assembled into a single range and then selected.

Sub SelectColumns()

  Dim AltRng As Range
  Dim LastCol As Variant
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    LastCol = Wks.Cells(1, Columns.Count).End(xlToLeft).Column
    
      For C = 6 To LastCol Step 2
        Set Rng = Wks.Cells(1, C)
        Set RngEnd = Wks.Cells(Rows.Count, C).End(xlUp)
        Set Rng = Wks.Range(Rng, RngEnd)
          If AltRng Is Nothing Then Set AltRng = Rng
          Set AltRng = Union(AltRng, Rng)
      Next C
      
      AltRng.Select
  
End Sub

Try

Sub Test()
For N = 6 To Columns.Count Step 2
    If Application.CountA(Columns(N)) = 0 Then Exit For
    'Your code here
    
Next N
End Sub

Hello Amy Kate,

Since you are not following standard conventions in your layout, it would help if you post your workbook. This will clear up any ambiguities or misunderstandings about what you want. If possible you should include a before and after example of the data.

Hello Amy Kate,

Thanks for posting the workbook. I revised the macro and attached it to button on the "After" sheet. When clicked it will select the columns from "F" to the last column with data. Here is the macro which has been added to the attached workbook.

Sub SelectColumns()

  Dim AltRng As Range
  Dim LastCol As Variant
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    LastCol = Wks.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
    
      For C = 6 To LastCol Step 2
        Set Rng = Wks.Columns(C)
          If AltRng Is Nothing Then Set AltRng = Rng
          Set AltRng = Union(AltRng, Rng)
      Next C
      
      AltRng.Select
  
End Sub