VBA Code To Select Both Contiguous & Non-Contiguous Columns

[Jiraya_00]

Hi All,

I need help writing a code that would allow me to select both contiguous and non-contiguous columns. For example, in the image provided below, I would like to select all data within the used range for columns B through D and M through O and copy and paste values into a new sheet within the workbook.

Thank you in advance for your help!

 

[Peter_SSs]

Does this do what you want?

Sub Copy_Data()
  With ActiveSheet
    Sheets.Add After:=Sheets(.Index)
    Intersect(.UsedRange, Union(.Columns("B:D"), .Columns("M:O"))).Copy Destination:=Sheets(.Index + 1).Range("A1")
  End With
End Sub

 

[kvsrinivasamurthy]

Try.

Sub CopyUsedRange()
Dim Sh As Worksheet
Dim Lc&, T&, Ros, S$
Application.ScreenUpdating = False
Set Sh = ActiveSheet
Sh.Copy After:=Sh
ActiveSheet.Name = "New"
Lc = Cells(1, Columns.Count).End(xlToLeft).Column
Ros = Rows.Count
For T = 1 To Lc
If Cells(1, T).End(xlDown).Row = Ros Then
S = S & "," & Cells(1, T).Address
    If Len(S) > 240 Or (T = Lc And S <> "") Then
    Range(Mid(S, 2)).EntireColumn.Delete
    S = ""
    End If
End If
Next T
Application.ScreenUpdating = True
End Sub

 

[Jiraya_00]

Thank you both for these codes as they both work! I went with Peter's code for simplicity (less number of code lines).

Also, I understand the purpose of union is to join the non-contiguous columns together, but what exactly does intersect do here? And in what instances would one want to use intersect?

 

[Peter_SSs]

but what exactly does intersect do here?

Each column has over 1,000,000 rows. Intersecting the entire columns with the worksheet's used range could be expected to reduce the number of copied cells to a much lower number. For example, if the used range was only say 100 rows then there would only be 600 cells to copy instead of more than 6 million. In addition, it is what you asked for.

..all data within the used range for columns B through D and M through O ..

 

[Jiraya_00]

Ahhh, I understand now. Thanks Peter!

 

[Peter_SSs]

You're welcome.