2

I’m trying to write a bit of VBA which will check the value of a cell in a range of columns (rows M to GD), if the cell does not contain “YY” delete the column.

The cell to check is always in row 22

I’ve tried the following, but it’s seriously slow.

w = 186
Do
If Worksheets(“SOF”).Cells(22, w).Formula = "YY" Then
w = w - 1
Else
   Worksheets(“SOF”).Cells(22, w).EntireColumn.Delete
End If
w = w - 1
Loop Until w < 13

Does anyone have any suggestions on how to speed this up or a better way to do this problem?

Thanks

BlueFrog
  • 131
  • 1
  • 8

1 Answers1

4

Does anyone have any suggestions on how to speed this up or a better way to do this problem?

Yup there is. Do not delete the columns in a loop. Use the Union method. Here is an example. I have commented the code so you will not have a problem understanding it. Still if you do then simply post back.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim i As Long
    Dim delRange As Range

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("SOF")

    With ws
        '~~> Loop through relevant columns
        For i = 13 To 186
            '~~> Check if the value is equal to YY
            If UCase(Trim(.Cells(22, i).Value)) = "YY" Then
                '~~> Store the Range to delete later
                If delRange Is Nothing Then
                    Set delRange = .Columns(i)
                Else
                    Set delRange = Union(delRange, .Columns(i))
                End If
            End If
        Next i
    End With

    '~~> Delete the relevant columns in one go
    If Not delRange Is Nothing Then delRange.Delete
End Sub

This would execute in a blink of an eye but if you want, you can sandwich the code between Application.ScreenUpdating = False and Application.ScreenUpdating = True

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • +1, this is great, I'll have to use this sometime when I delete rows. – enderland Jan 28 '14 at 20:44
  • @enderland: Using that method to delete rows has been covered in the past in SO :) [Link1](http://stackoverflow.com/questions/20077945/delete-cells-in-an-excel-column-when-rows-0), [Link2](http://stackoverflow.com/questions/19195515/trying-to-delete-a-row-if-no-data-in-row-aj) and many more if you search SO – Siddharth Rout Jan 28 '14 at 20:50
  • 1
    @SiddharthRout I was more making a note to self to use `Union` when deleting rows :) Generally I'm not deleting enough to matter, but, it'd definitely avoid that annoying "make sure you iterate backwards through rows correctly" problem. – enderland Jan 28 '14 at 21:00
  • It worked like a charm, thanks very much for your help. I'm going to clue myself up on Unions, not used them until now. – BlueFrog Jan 29 '14 at 17:08