+- 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.