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.

"I have a 2D array" - looks like you're working with a Range though, not an array? Would be useful to add more code so we can see where things like End_Of_Quarter are coming from. — Tim Williams
– Tim Williams, Commented Dec 5, 2024 at 18:51

Tim Williams · Accepted Answer · 2024-12-05 18:56:40Z

0

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

answered Dec 5, 2024 at 18:56

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I set up range based on loop counter?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related