how to replace the blanks in a particular range of the excel.
how can we do it.how to do it for the particular range.
You can use the Find/Replace activity to replace with in the specified range in the excel.
Hope it helps!!
it is not accepting blanks
Blanks means no data in the cell right or blank word, share an image how it looks like… @anjani_priya
no data in cell.
cheers
1 Like
Okay @anjani_priya
For that you can use the below Vb code in Invoke vba activity, Store the below code in a Notepad file and pass the method name and notepad file path in the invoke vba activity,
Sub ReplaceEmptyCells()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
Dim cell As Range
For Each cell In ws.UsedRange
If IsEmpty(cell) Then
cell.Value = "abc"
End If
Next cell
End Sub
Check the below workflow for better understanding,
Input -
Output -
Hope it helps!!
where should I specify the range
Change the code as below to specify code,
Sub ReplaceEmptyCells()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
Dim cell As Range
For Each cell In ws.Range("A2:B5")
If IsEmpty(cell) Then
cell.Value = "abc"
End If
Next cell
End Sub
In this line you can specify the range “For Each cell In ws.Range(“A2:B5”)”
Hope it helps!!
it there any alternative other than vba
You can use find/replace activity and in the value field give Type.Missing … this should find all the blank values and replace with any text you give
You can check the match entire cell contents check box
Hope this helps
Cheers
i have done but iam not getting 0’s still it is in blank
Check the formatting of the cell may be the cell is formatted to show 0 as blank
To check open excel…highlight a cell and see the value in the top of excel and see if it shows 0 or blank
cheers
This is the best way than other methods.
We can use Type.Missing in Find,/Replace activity but it’s not working some times.
You can pass the range and sheet name to the code by using arguments.
Hope you understand!!
is it possible to share the excel there may be some other characters we can check…like a space or any other character
or
you can read the data into datatable then replace the values and then write back…are you good with that?
cheers
You can do it by read the excel and storing in a datatable and do it,
→ Use read range workbook activity to read the excel and store in a datatable.
→ Use for each activity to iterate the each row in the datatable. dt.rows
→ Inside for each insert one more for each activity to iterate each column in the datatable. dt.Columns
→ Inside this for each insert If condition to check the condition,
- Condition -> String.IsNullOrEmpty(Row(Column).ToString().Trim())
→ Inside If condition insert Assign activity to write the 0 in empty cells,
- Assign -> Row(Column) = Cint("0")
→ Outside of for each insert the Write range workbook activity to write the datatable to excel.
Check the below workflow,
Sequence 2.xaml (10.5 KB)
Hope it helps!!
no result.I havent got result
Share your input file then I’ll give you the workflow to replace space with 0… @anjani_priya
sheet1.xlsx (9.7 KB)
in sheet