how to remove every seventh column from excel

  • Thread starter Thread starter Smoki
  • Start date Start date
S

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
 
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
 
THANK YOU, you are great, and it works :)

Smoki

Jacob Skaria said:
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
Click to expand...
 
Back
Top