Add gridlines etc to cells in Excel

[Kaydo]

Hi,

I have a few things in Excel I have not been able to figure out. I would greatly appreciate some advice.

1. How can I add gridlines to cells in excel aka “Format cells → Border”?

2. How can I delete the entire contents of a cell based on one word? Currently if I try find and replace activity, the word is deleted from the cell but the rest of the words remain. If a cell contains a certain word, I want all of the cells contents emptied/cleared.

3. How can I delete empty rows?

Thanks so much!

[ali.koundi1 (Ali Koundi)]

Hi ,

For first point check :

How to give border by using uipath dynamically

Hi @anjani_priya [image] Input (1).txt (539 Bytes) After running the process below is the output. Book 1 (1).xlsx (13.1 KB) Regards

Delete cell in excel with specific value

Yah that’s possible —use excel application scope and pass the file path of excel as input —inside the scope use READ RANGe Activity and get the output with a variable of type datatable named dt —now use a for each row loop and pass the above variable dt as input —inside the loop use IF Condition like this row(“yourcolumnname”).ToString.Equals(“0”) If it’s true it will go to THEN apart where we can use a simple assign activity like this row(“yourcolumnname”) = String.Empty Cheers @Manikan…

Delete empty rows in excel

@Aregala_Yedukondalu , If that is the case, then we can perform a Clear Data operation using Write Cell Activity first on the Sheet, then you can use Write Range Activity on the Same sheet. If you have access to Modern Excel Activities, then you could try with the below Approach as well using Clear Sheet/Range/Table Activity. [image] If modern activities are not preferred, then you could check with the below post on using Write Cell Activity to Clear the Sheet.

I hope be useful,
Regards.

[Kaydo]

Thank you. I have been able to fix item #2 regarding deleting cells in Excel with a specific value.

My highest priority now is item #3, deleting the blank rows. Anybody got any insight into how I can do this? I looked at the mentioned post and it doesn’t seem to specifically address my issue. I am using “extract data table” action to grab data from three webpages and write the info to excel.

Is there a way to delete all empty rows on the excel sheet after the data is written?

[rashmisingh2695 (Rashmi)]

Hi,

It would be a better approach if you delete the empty rows before writing table data to excel. You can use filter datatable to remove empty rows from the datatable output of “extract data table”.

But If you want to delete rows from excel you can try below steps

1. Read the excel data and store the data in a DataTable variable using Read Range activity inside the Excel Application Scope.
2. Use the For Each Row activity to iterate through the rows.
3. Inside the loop, use the If condition to check if the row is empty. Then, use the Delete Row activity for rows that meet the condition.
   You can use the following condition: row.ItemArray.All(Function(x) String.IsNullOrEmpty(x.ToString.Trim))

[ashokkarale]

@Kaydo,

For Point 3 use this code. It’s using VBA Code so make sure your change excel settings as below.

Code:

VBA Code: Make sure you change the sheet name as per your requirement in VBA Code.txt file

Sub RemoveBlankRows()
    Dim Rng As Range
    Dim i As Long
    Set Rng = Thisworkbook.Sheets("Sept 11").UsedRange
    For i = Rng.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(Rng.Rows(i)) = 0 Then
            Rng.Rows(i).EntireRow.Delete
        End If
    Next i
End Sub

RemoveBlankRows.zip (70.0 KB)

Thanks,
Ashok

[mohamed.shaker]

For point 3, you have two options:

1. Use VBA code to remove the empty rows, as @ashokkarale suggested—this is an efficient solution.
2. Alternatively, you can:

• Get the row count of the current DataTable.
• Filter out the empty rows.
• Use the ‘Add Data Row’ activity in a loop to append empty rows to the filtered DataTable until its row count matches the original DataTable.

When you write this updated table back to Excel, the data will overwrite the existing rows, and the additional empty rows will clear any remaining data below. Although this method might require more processing time for large Excel sheets, it’s a practical option for environments where VBA can’t be enabled due to security restrictions.