CODEWITHSUNDEEP
excelvba
User123456789
User123456789

Reputation: 149

VBA Excel - Find a certain column and move it to the column before another column in Excel

This is an example: enter image description here

I would like to find the last column (the first column from left, where the column to its right is empty - in this case column F). Then I would like to move this column to the left of the column with header "D/G/B.x" (in this case column H).

Currently I'm able to search a column due to the header text and move it to another column position, this is not exactly what I'm trying to accomplish, but my try to obtain the goal:

Here is my current VBA:

Sub Test()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet
For Each ws In Worksheets
    ws.Activate
    Dim search As Range
    Set search = ws.Rows("1:1").Find("D/G/B", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not search Is Nothing Then
        Application.CutCopyMode = False
        search.EntireColumn.Cut
        ws.Columns("H").Select
        Selection.Insert Shift:=xlToRight
        Application.CutCopyMode = False
    End If
Next
Application.ScreenUpdating = True
End Sub

Any help is appreciated.

Upvotes: 0

Views: 301

Answers (1)

SJR
SJR

Reputation: 23081

Think this does what you want.

Sub Test()

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim starting_ws As Worksheet
Dim c As Long
Set starting_ws = ActiveSheet

For Each ws In Worksheets
    Dim search As Range
    Set search = ws.Rows("1:1").Find("D/G/B", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not search Is Nothing Then
        c = ws.Cells(1, search.Column).End(xlToLeft).Column
        If c < search.Column - 1 Then 'only move if not adjancent
            ws.Columns(c).Cut search.Cells(1).Offset(, -1)
        End If
    End If
Next

Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions