VBA - Delete Columns Excel 2010

[shemayisroel]

Hi,


My data range starts from C1:DH3.

I need some code that starting from D1 deletes 7 columns, misses a column then keeps on deleting 7 columns misses a column etc up until Column DH.

Using the example above, the first couple of deletes will delete columns D-J keep column K, delete columns L-R keep S etc

Thanks

 

[Rick Rothstein]

Does this do what you want?

Sub Delete7ColumnsKeep1()
  Dim X As Long
  For X = 4 To 112 Step 8
    Columns(X).Resize(, 7).Delete
  Next
End Sub

 

[shemayisroel]

Does this do what you want?

Sub Delete7ColumnsKeep1()
  Dim X As Long
  For X = 4 To 112 Step 8
    Columns(X).Resize(, 7).Delete
  Next
End Sub

I'll try it now and let you know how it goes - thanks for the quick response

 

[shemayisroel]

Just tried it - it deletes the first 7 columns but stops there...

 

[Rick Rothstein]

Sorry, I rushed that code out just a little too fast. Try this instead...

Sub Delete7ColumnsKeep1()
  Dim X As Long, LastCol As String
  LastCol = "DH"
  Application.ScreenUpdating = False
  For X = 8 * Int(Cells(1, LastCol).Column / 8) - 4 To 4 Step -8
    Columns(X).Resize(, 7).Delete
  Next
  Application.ScreenUpdating = True
End Sub

 

[shemayisroel]

Sorry, I rushed that code out just a little too fast. Try this instead...

Sub Delete7ColumnsKeep1()
  Dim X As Long, LastCol As String
  LastCol = "DH"
  Application.ScreenUpdating = False
  For X = 8 * Int(Cells(1, LastCol).Column / 8) - 4 To 4 Step -8
    Columns(X).Resize(, 7).Delete
  Next
  Application.ScreenUpdating = True
End Sub

Tested with a small sample range and it works - thank you so kindly!