REMOVE EXCEL FORMATTING

Help
1

Hi,
Is there any way to delete the formatting of empty cells? So I used a excel template as input file, bot will fill in the template with results and later there are unused formatted rows and cells which have to made into normal cells. How can I achieve this?

2

Use the invoke VBA actvitiy and:

Public Function RemoveFormattingEmptyCells()

  Dim sheet As Worksheet
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  
  For Each sheet In Worksheets
      
        sheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats
    Next sheet

  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True

End Function
3

should I pass the sheet name or file name as variable?

4

The file name is not needed as the Invoke VBA actvitiy should be placed insiade an Excel Application Scope.

As for sheet name, do you wish to cover all sheets? This will do that. It will only take a small edit to make it look to at a specified sheet only:

InvokeVBA2.zip (9 KB)

5

I want it for a specific sheet only. How would the code change apart from removing the for each loop?

6

This example looks at the Data Worksheet only:

Public Function RemoveFormattingEmptyCells()

  Dim sheet As Worksheet
  Application.Calculation = xlCalculationManual
  Application.ScreenUpdating = False
  
	Worksheets("Data").Activate
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).ClearFormats

  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True

End Function

InvokeVBA3.zip (9.3 KB)

2 Likes
7

Hi ronan,

Getting an error telling that "Subscripts out of Range " for the same code for a specific sheet that you have shared above. Any idea on solving that?

8

It sounds as if you are trying to access a sheet that doesn’t exist. Did you change the sheet name here:

Worksheets("Data").Activate

??

The workflow I attached works fine.

9

Thanks Ronan, it works perfectly. :slight_smile:

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.