Home

Removing Formula from Excel Blank Cells automatically

%3CLINGO-SUB%20id%3D%22lingo-sub-666457%22%20slang%3D%22en-US%22%3ERemoving%20Formula%20from%20Excel%20Blank%20Cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666457%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20I%20have%20a%20code%20that%20I%20found%20on%20the%20Internet%20for%20removing%20formula%20from%20excel%20blank%20cells%20but%20it%20works%20only%20for%20one%20worksheet%20with%20a%20specific%20name.%20I%20was%20wondering%20if%20someone%20could%20change%20it%20so%20it%20will%20work%20for%20several%20sheets%20with%20different%20names%20and%20ranges.%20For%20example%20sheetA%20range(a1%3Ab20)%20%2C%20sheetB%20range(a1%3Ae40)%2C%20sheetC%20range(a1%3An15)%3C%2FP%3E%3CP%3ETHANKS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Macro1()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Each%20c%20In%20Worksheets(%22your%20sheet%20name%22).Range(%22your%20range%22)%3C%2FP%3E%3CP%3EIf%20c.Value%20%3D%20%22%22%20Then%20c.Select%3A%20Selection.ClearContents%3CBR%20%2F%3ENext%20c%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-666457%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666529%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20Formula%20from%20Excel%20Blank%20Cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666529%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F258032%22%20target%3D%22_blank%22%3E%40Edrisbas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20I%20hope%20that%20I%20have%20understood%20the%20task%20correctly.%3C%2FP%3E%3CP%3Etry%20this..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20RemoveFormulasFromEmptyCells()%3CBR%20%2F%3EDim%20wkstab%20As%20Worksheet%3CBR%20%2F%3EDim%20rngCell%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20wkstab%20In%20Worksheets%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20rngCell%20In%20wkstab.UsedRange%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20rngCell.HasFormula%20And%20rngCell.Value%20%3D%20%22%22%20Then%3CBR%20%2F%3E%20rngCell.ClearContents%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20rngCell%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20wkstab%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-Tanker.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%3C%2FA%3E%20-%20a%20database%20full%20of%20excel-macros%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666565%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20Formula%20from%20Excel%20Blank%20Cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20response.%20I%20tried%20but%20it%20doesn't%20work.%20there's%20an%20error%20with%20this%20line%3A%3C%2FP%3E%3CP%3EIf%20rngCell.HasFormula%20And%20rngCell.Value%20%3D%20%22%22%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666729%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20Formula%20from%20Excel%20Blank%20Cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F258032%22%20target%3D%22_blank%22%3E%40Edrisbas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecould%20you%20append%20your%20file%2C%20please%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-666874%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20Formula%20from%20Excel%20Blank%20Cells%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-666874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sorry%20the%20file%20doesn't%20belong%20to%20me%20so%20I%20don't%20have%20the%20right.%20It's%20weird%20it%20works%20but%20it%20shows%20me%20error%20number%2013%20at%20the%20end.%20But%20anyway%20it%20doesn't%20solve%20my%20problem%20because%20when%20I%20save%20the%20file%20as%20tab%20delimited%20or%20CSV%20there%20are%20the%20sign%20%3B%20in%20the%20blank%20cells%20containing%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Edrisbas
Occasional Contributor

Hello. I have a code that I found on the Internet for removing formula from excel blank cells but it works only for one worksheet with a specific name. I was wondering if someone could change it so it will work for several sheets with different names and ranges. For example sheetA range(a1:b20) , sheetB range(a1:e40), sheetC range(a1:n15)

THANKS.

 

Sub Macro1()

 

For Each c In Worksheets("your sheet name").Range("your range")

If c.Value = "" Then c.Select: Selection.ClearContents
Next c

End Sub

4 Replies

@Edrisbas 

Hi, I hope that I have understood the task correctly.

try this..

 

Sub RemoveFormulasFromEmptyCells()
Dim wkstab As Worksheet
Dim rngCell As Range

For Each wkstab In Worksheets

For Each rngCell In wkstab.UsedRange

If rngCell.HasFormula And rngCell.Value = "" Then
rngCell.ClearContents
End If

Next rngCell

Next wkstab

End Sub

 

Regards

Bernd

www.vba-Tanker.com - a database full of excel-macros

@Berndvbatanker 

Thanks for the response. I tried but it doesn't work. there's an error with this line:

If rngCell.HasFormula And rngCell.Value = "" Then

 

@Edrisbas 

could you append your file, please?

@Berndvbatanker 

I'm sorry the file doesn't belong to me so I don't have the right. It's weird it works but it shows me error number 13 at the end. But anyway it doesn't solve my problem because when I save the file as tab delimited or CSV there are the sign ; in the blank cells containing formulas.