Clean column if emty header

[doriannjeshi]

Hi,
I need a macro that Cleans the column of all content, formats if emty header , to the last column of the range.

 

[HighAndWilder]

Hi,
I need a macro that Cleans the column of all content, formats if emty header , to the last column of the range.

Assuming that the header is in row 1.

Try this on a copy of your data.

Change the sheet name where indicated.

Private Sub subClearAll()
Dim rng As Range

  ActiveWorkbook.Save

  For Each rng In Worksheets("Sheet1").UsedRange.Rows(1).Cells '<- Change 'Sheet1' to your sheet name.

    If Len(Trim(rng.Value)) = 0 Then
    
      rng.EntireColumn.Cells.Clear
  
    End If

  Next rng
  
End Sub

 

[doriannjeshi]

Assuming that the header is in row 1.

Try this on a copy of your data.

Change the sheet name where indicated.

Private Sub subClearAll()
Dim rng As Range

  ActiveWorkbook.Save

  For Each rng In Worksheets("Sheet1").UsedRange.Rows(1).Cells '<- Change 'Sheet1' to your sheet name.

    If Len(Trim(rng.Value)) = 0 Then
   
      rng.EntireColumn.Cells.Clear
 
    End If

  Next rng
 
End Sub

 

[HighAndWilder]

View attachment 119477

What format are your column headers?

Can you use XL2BB to submit some data?

 

[doriannjeshi]

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	AQT	BTG		ATO	ARI		AVL	ADR	AFR	AKO	ASH	ALU	ALE		ITG	ITO	IMS	IVL	IOX
Sheet1

 

[HighAndWilder]

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	AQT	BTG		ATO	ARI		AVL	ADR	AFR	AKO	ASH	ALU	ALE		ITG	ITO	IMS	IVL	IOX
Sheet1

That's strange as it works for me. Try this code.

Private Sub subClearAllV2()
Dim rng As Range

  ActiveWorkbook.Save

  For Each rng In Worksheets("Sheet1").UsedRange.Rows(1).Cells '<- Change 'Sheet1' to your sheet name.

    If IsEmpty(rng.Value) Then
    
      rng.EntireColumn.Cells.Clear
  
    End If

  Next rng
  
End Sub

 

[doriannjeshi]

how to change this to activesheet ?

 

[HighAndWilder]

how to change this to activesheet ?

Private Sub subClearAllV2()
Dim rng As Range

  ActiveWorkbook.Save

  For Each rng In ActiveSheet.UsedRange.Rows(1).Cells '<- Change 'Sheet1' to your sheet name.

    If IsEmpty(rng.Value) Then
    
      rng.EntireColumn.Cells.Clear
  
    End If

  Next rng
    
End Sub

 

[doriannjeshi]

Thank you

 

[MARK858]

That's strange as it works for me.

Probably has a macro or variable called Trim that is confusing Excel