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?
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
should I pass the sheet name or file name as variable?
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)
I want it for a specific sheet only. How would the code change apart from removing the for each loop?
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)
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?
It sounds as if you are trying to access a sheet that doesn’t exist. Did you change the sheet name here:
The workflow I attached works fine.
Thanks Ronan, it works perfectly.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.