0

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.

User123456789
  • 149
  • 10

1 Answers1

1

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
SJR
  • 22,986
  • 6
  • 18
  • 26
  • 1
    Nice, thank you very much. I thought the "Activate" was necessary in a for loop, then I'll just delete that line, good you pointed that out. There isn't necessarily a gap between them. However, if that's not the case it's already in the right position. Otherwise I will just move column "D/G/B.x" one column to the right. Once again thank you. – User123456789 Dec 08 '20 at 16:10
  • 1
    Glad it worked. If there's no gap then it will then move the first column so I can add an If to avoid that. Re the activate, some reading matter: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Dec 08 '20 at 16:11