How do I set up range based on loop counter?

Question

I am new here and I do not have much experience. I greatly appreciate any advice you may provide.

I have a 2D array representing staff scheduled to work on a particular date. The data looks like this where "Staff" would be Initials such as KLK or SAS:

12/30/2024 12/31/2024 1/1/2025 1/2/2025 etc... Staff Staff Staff Staff Staff Staff Staff Staff 

I want to clean up the array such for dates containing staff names, all blank cells are removed and the staff names are moved to top of the column. I can't figure out once I've identified a column with data, how I can set up a range to "clean". For ex. if I get to 4th column and it has staff entries, I want to set up a range D2:D25 to clean.

 For J = 1 to End_Of_Quarter For i = 1 to Category_Limit 'On each date check to see if column is empty If (Cells(i,j).Value <> "" Then Counter = Counter + 1 End If Next i If Counter < Category_Limit 'Column is not empty. If column is empty want to move on to next column 'Want to set up Range "Staff" and clean it up. Range(Staff).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp End If Next J 

Thank you in advance for taking time to answer.

I did try using the command Range(Staff).SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp on the entire array but Excel interprets blank columns as data to be removed. I need the code to skip blank columns.

Answer 1

Something like this maybe:

Sub CleanUpColumns() Dim rngData As Range, col As Range Set rngData = ActiveSheet.Range("A1:D6") 'your data table For Each col In rngData.Columns 'check each column col.SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp Next col End Sub