Select Multiple Columns

Question

I want to select multiple columns from "A23" down to its last row and "D23:H23" down to its last row at the same time but I am having trouble. Below is my current code but it selects the columns individual. Can someone please help? These columns will be dynamic in nature where the number of rows may vary. There could also be instances where there columns are empty and I may have trouble with the xldown function.I will attache a picture of how I would like the code to work below

Sub selectColumns()
Dim lrow As Long
Dim lrow2 As Long

lrow = Range(Range("A23"), Range("A23").End(xlDown)).Select
lrow2 = Range(Range("D23:H23"), Range("D23:H23").End(xlDown)).Select
End Sub

See [Copy-Paste non-contiguous ranges](https://stackoverflow.com/questions/55690218/copy-paste-non-contiguous-ranges). You can do: `Set rng = Union(rng1, rng2)`, then `rng.select` will work. But perhaps also have a look at [Avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). To find a last row or column, check [Find Last Cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — ouroboros1, May 24 '22 at 06:59
Does this answer your question? [Copy-Paste non-contiguous ranges](https://stackoverflow.com/questions/55690218/copy-paste-non-contiguous-ranges) — Dominique, May 24 '22 at 07:22
@Dominique not quite there yet, I am still having difficulty. I have updated the question with a picture of what I am after. — jovi679, May 24 '22 at 22:42

VBasic2008 · Answer 1 · 2022-05-24T07:26:00.667

Select `ActiveSheet` Columns

Sub SelectColumns()

    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim rg As Range
    Dim lCell As Range
    
    With ws.Range("A23")
        Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If Not lCell Is Nothing Then Set rg = .Resize(lCell.Row - .Row + 1)
    End With
         
    With ws.Range("D23:H23")
        Set lCell = .Resize(ws.Rows.Count - .Row + 1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If Not lCell Is Nothing Then
            If rg Is Nothing Then
                Set rg = .Resize(lCell.Row - .Row + 1)
            Else
                Set rg = Union(rg, .Resize(lCell.Row - .Row + 1))
            End If
        End If
    End With

    If Not rg Is Nothing Then rg.Select

End Sub

Thanks for your replies, I apologies I forgot to mention in the question description that there are some wiritng description below the ranges I need and I do not wish to select them. Please refer to a picture I have included to preview of what I am trying to accomplish — jovi679, May 24 '22 at 22:43

Select Multiple Columns

1 Answers1

Select ActiveSheet Columns

Select `ActiveSheet` Columns