Order and Hide columns using VBA

[davidtaylor598]

Hi all,

I have X amount of worksheets, all with the same column headings (created with a macro below). I need to hide certain columns (C,D, E) and re-order the remaining ones to A, F,B. Is there a quick way to do this? I guess it will have to be somewhere inside the loop below.

Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("C2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Range("C" & i).Value <> .Range("C" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("C" & iStart).Value
            On Error GoTo 0
            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Noz2k]

To hide the columns you want to add lines like

If Range("c1").EntireColumn.Hidden = False Then
Range("c1").EntireColumn.Hidden = True
End If

into your loop, for each relevant column.

As for reordering the columns there might be an easier way, but the only thing I can think of to do is to create some variables which determine the last row in the columnF, cut the range from row1 to the last row, paste in an unused column (you don't want to paste over the values in column B), do the same with column B.

And then use the same process on the new positions of the data, and pasting back into the relevant column.