how to remove every seventh column from excel

[Smoki]

Hello,
does anyone know how to remove every seventh column from my excel. I know I
can do that manually, but there are too many columns, so, if there any easier
way to do this, instead of deleting one by one?

Thanks,
Smoki

 

[Jacob Skaria]

The best solution would be to use a macro to delete every 7th column. 7, 14,
21 etc; Since it is not sure you want to delete the column or remove the
contents I have written both..You can try out the below macros. If you are
new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

Sub RemoveDatafrom7thColumn()
'Macro to remove contents
Dim lngCol As Long, lngLastCol As Long
lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For lngCol = 7 To lngLastCol Step 7
Columns(lngCol).ClearContents
Next
End Sub


Sub Delete7thColumn()
'Macro to delete the columns
Dim lngCol As Long, lngLastCol As Long
lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
lngLastCol = (1 + Int(lngLastCol / 7)) * 7
For lngCol = lngLastCol To 1 Step -7
Columns(lngCol).Delete
Next
End Sub

If this post helps click Yes

 

[Smoki]

THANK YOU, you are great, and it works

Smoki

The best solution would be to use a macro to delete every 7th column. 7, 14,
21 etc; Since it is not sure you want to delete the column or remove the
contents I have written both..You can try out the below macros. If you are
new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

Sub RemoveDatafrom7thColumn()
'Macro to remove contents
Dim lngCol As Long, lngLastCol As Long
lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For lngCol = 7 To lngLastCol Step 7
Columns(lngCol).ClearContents
Next
End Sub


Sub Delete7thColumn()
'Macro to delete the columns
Dim lngCol As Long, lngLastCol As Long
lngLastCol = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
lngLastCol = (1 + Int(lngLastCol / 7)) * 7
For lngCol = lngLastCol To 1 Step -7
Columns(lngCol).Delete
Next
End Sub

If this post helps click Yes