How to clear all data EXCEPT columns X, Y & Z?

Question

I would like to clear ALL (not delete) the content in a worksheet EXCEPT columns X, Y & Z (for example)? These columns are stored in a variable.

Use your macro recorder and select all the columns you want to clear, then press delete. When you look at the code you will see it is the same as clearing contents. — Davesexcel, Jul 04 '15 at 18:37

SQL Police · Accepted Answer · 2015-07-04T20:54:06.843

8

Yes, you clear two ranges:

Range No. 1 from column 1 ('A') to 23 ('W').
Range No. 2 from column 27 ('AA') to the last used column.

This function does it:

Public Sub CustomClear()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    ws.Range(ws.Columns(1), ws.Columns(23)).Clear
    ws.Range(ws.Columns(27), ws.Columns(ws.UsedRange.End(xlToRight).Column)).Clear
End Sub

edited Jul 04 '15 at 20:54

answered Jul 04 '15 at 18:44

SQL Police

4,127
1
25
54

Hi! How could I modify the above code to also only delete row 8 and further. Thanks – Prince M Jun 16 '22 at 01:39

score 0 · Answer 2 · answered Jul 06 '15 at 04:20

Really interesting question -- and @SQLPolice's answer succinctly gets the job done. (+1 on that by the way.)

Here's another option, which can handle Range variables that start / stop in columns other than Range("X:Z"):

Option Explicit
Public Sub ClearRangesBeforeAndAfter()

    Dim rngToKeep As Range, rngToClear As Range
    Dim lngColNum As Long
    Dim wks As Worksheet

    Set wks = ThisWorkbook.Worksheets(1) '<~ assume we're on Sheet1

    With wks
        Set rngToKeep = .Range("W:Z") '<~ this is the example range from OP
        '
        'Or, we could pick any other group of continuous columns, like:
        '
        'Set rngToKeep = .Range("B:D")
        'Set rngToKeep = .Range("H:Z")
        'Set rngToKeep = .Range("A:AZ")

        'First, find the farthest-left border of the "keep" range
        'by leveraging the relative nature of rngToKeep.Cells
        lngColNum = rngToKeep.Cells(1, 1).Column

        'Now we can clear everything up to that border
        If lngColNum > 1 Then
            Set rngToClear = .Range(.Columns(1), .Columns(lngColNum - 1))
            rngToClear.Clear
        End If

        'Then, find the farthest-right border of the "keep" range
        'and clear the remaining cells
        lngColNum = rngToKeep.Offset(0, rngToKeep.Columns.Count).Column
        Set rngToClear = .Range(.Columns(lngColNum), _
                                .Columns(.Columns.Count))
        rngToClear.Clear
    End With

End Sub

How to clear all data EXCEPT columns X, Y & Z?

2 Answers2

Linked