QP School
Loop through columns and clear contents - Printable Version

+- QP School (https://qomplainerzschool.lima-city.de)
+-- Forum: Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=3)
+--- Forum: Excel VBA Tutorials (https://qomplainerzschool.lima-city.de/forumdisplay.php?fid=48)
+--- Thread: Loop through columns and clear contents (/showthread.php?tid=5269)



Loop through columns and clear contents - Qomplainerz - 07-27-2023

Example:

Sub ClearContentsInColumns()
    Dim ws As Worksheet
    Dim lastColumn As Long
    Dim col As Long

    'Set the worksheet where the data is located
    Set ws = ThisWorkbook.Sheets("Sheet1")

    'Find the last column with data in the worksheet
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    'Loop through each column and clear the contents
    For col = 1 To lastColumn
        ws.Columns(col).ClearContents
    Next col
End Sub

Explanation:

The code defines a subroutine named ClearContentsInColumns.
It declares variables for the worksheet (ws), the last column with data (lastColumn), and a loop counter (col).
Set ws = ThisWorkbook.Sheets("Sheet1") specifies the worksheet ("Sheet1") where the data is located.
The lastColumn variable is determined using ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column, which finds the last column with data in the worksheet.
The For loop iterates through each column from 1 to lastColumn.
ws.Columns(col).ClearContents clears the contents of each cell in the current column (col) of the worksheet.