How to loop though different ranges
Requirement:
The user is trying to loop through 4 different ranges, and clear contents.
What the user has at present is:
Code:
Sub Clear()
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Set Rng1 = Sheets("Input").ListObjects("Data").ListColumns("Days Worked").DataBodyRange
Set Rng2 = Sheets("Input").ListObjects("Data").ListColumns("O/T Hours Worked").DataBodyRange
Set Rng3 = Sheets("Input").ListObjects("Data").ListColumns("Commission Value").DataBodyRange
Set Rng4 = Sheets("Input").ListObjects("Data").ListColumns("Advance").DataBodyRange
For Each cell In Rng1
cell.ClearContents
Next
For Each cell In Rng2
cell.ClearContents
Next
For Each cell In Rng3
cell.ClearContents
Next
For Each cell In Rng4
cell.ClearContents
Next
End Sub
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1215864-loop-though-different-ranges
Solution:
Code:
Sub Clear()
Application.ScreenUpdating = 0
[Data[Days Worked]].ClearContents
[Data[O/T Hours Worked]].ClearContents
[Data[Commission Value]].ClearContents
[Data[Advance]].ClearContents
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by KjBox.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to cut, copy and insert on a loop |
| How to turn one operation into a loop in VBA |
| How to use looping to delete cells of similar value |
| How to use loop IF, if range is unknown |
| How to loop a macro with various length columns |
| How to change reference columns in another worksheet using VBA looping |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
