SOLVED

VBA code for blank cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2614925%22%20slang%3D%22en-US%22%3EVBA%20code%20for%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2614925%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20fill%20all%20the%20blank%20cells%20with%20%22%3CSTRONG%3EUNASSIGNED%20CUSTOMER%3C%2FSTRONG%3E%22%20string.%20The%20problem%20i%20am%20facing%20is%20specifying%20the%20range%20of%20the%20loop%20that%20i%20want%20to%20run.%3C%2FP%3E%3CP%3ESince%20I%20want%20to%20make%20this%20as%20dynamic%20as%20possible.%3C%2FP%3E%3CP%3EI%20tried%20this%20code%2C%20but%20not%20working%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20valueCells%20As%20Range%3C%2FP%3E%3CP%3EDim%20valueRange%20As%20Range%3C%2FP%3E%3CP%3Eset%20valueRange%20%3D%3C%2FP%3E%3CP%3EFor%20Each%20valueCells%20In%20valueRange%3C%2FP%3E%3CP%3EIf%20VBA.IsEmpty(valueCells.Value)%20%3D%20True%20Then%3C%2FP%3E%3CP%3EvalueCells.Value%20%3D%20%22Customer%20Unassigned%22%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3ENext%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20please%20help%20me%20out%20with%20the%20range%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--Or--%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20should%20i%20write%20in%20VBA%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20attached%20file..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2614925%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-2615042%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20for%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2615042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can't%20just%20write%20some%20lines%20of%20code%20in%20a%20module%3B%20you%20should%20create%20a%20macro%20or%20a%20function.%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20FillTheBlanks()%0A%20%20%20%20ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value%20%3D%20%22UNASSIGNED%20CUSTOMER%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hello Everyone,

I am trying to fill all the blank cells with "UNASSIGNED CUSTOMER" string. The problem i am facing is specifying the range of the loop that i want to run.

Since I want to make this as dynamic as possible.

I tried this code, but not working - 

 

Dim valueCells As Range

Dim valueRange As Range

set valueRange =

For Each valueCells In valueRange

If VBA.IsEmpty(valueCells.Value) = True Then

valueCells.Value = "Customer Unassigned"

End If

Next

 

Can you please help me out with the range here?

 

--Or--

 

What should i write in VBA code?

 

Please help..??

 

Here is a attached file..

4 Replies

@Zan_Hanifee 

You can't just write some lines of code in a module; you should create a macro or a function.

Try this:

Sub FillTheBlanks()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = "UNASSIGNED CUSTOMER"
End Sub
Sir, What is the working of SpecialCells(xlCellTypeBlanks) ?
best response confirmed by Zan_Hanifee (Regular Contributor)
Solution

@Zan_Hanifee 

It returns the range containing all blank cells in the range specified before SpecialCells, in this code the used range of the active sheet.

See Range.SpecialCells method (Excel) and XlCellType enumeration (Excel) 

Thank you so much sir