CODEWITHSUNDEEP
vbaexcel
erokt151
erokt151

Reputation: 1

VBA: Deleting particular columns in a range

I'm working on a project and I need a program that will delete the empty columns (other than the header) in columns A through F and column J. I'd like it to loop through Range("A10:F10000") and Range("J:J") and delete the columns from cell A10, B10, etc and down, then shift the remaining data left. I was working with this, that affects the whole worksheet instead of just a range:

ecl = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Column

For cl = ecl To 1 Step -1
    cnt = Application.WorksheetFunction.CountA(Sheet1.Columns(cl))
    If cnt = 1 Then
        Sheet1.Columns(cl).Delete
    End If

Next

I usually can use formulas and am not super familiar with VBA, but for this project it has to be a macro. I hope this makes sense and would appreciate any advise!

Upvotes: 0

Views: 738

Answers (1)

DisplayName
DisplayName

Reputation: 13386

you could use this:

Dim cl As Range, colsToDelete As Range
With ActiveSheet 'reference wanted sheet
    Set colsToDelete = .UsedRange.Columns(.UsedRange.Columns.Count).Offset(, 1).Resize(1, 1) ' initialize 'colsToDelete' to a "dummy" range certainly out of relevant one
    With Intersect(.Range("A10:J" & .UsedRange.Rows(.UsedRange.Rows.Count).Row), .Range("A:F, J:J")) ' reference referenced sheet range in column A to F and G form row 10 down to referenced sheet last not empty cell row
        For Each cl In .Columns ' lop through referenced range columns
            If Application.WorksheetFunction.CountA(cl) = 1 Then Set colsToDelete = Union(colsToDelete, cl) 'if current column is empty (i.e. only header in row 10) then add it to the colsToDelete range
        Next
        Set colsToDelete = Intersect(colsToDelete, .Cells) ' get rid of the "dummmy" range
    End With
End With
If Not colsToDelete Is Nothing Then colsToDelete.EntireColumn.Delete ' if any range to delete, do it!

Upvotes: 1

Related Questions