Delete empty rows (Position will be change)


I have to automate an excel sheet. In this sheet I need to remove empty sheet rows. When it appears. (But this empty rows position will be change. E:G Empty rows in (A67) (A56) , Tomorrow row position will change to (A80),(A45).
Here with the attached excel sheet
Sample 1.xlsx (8.7 KB)

Can anyone please advise me on this.
Thank you.

Hi @Udesheyy ,

Use below code in your macro, it will remove empty rows.

Public Function DeleteEmptyRows()
Dim xlWorkbook As Workbook
Dim xlWorksheet As Worksheet
Dim xlRange As Range
Dim lastUsedRow As Long
Dim column1 As String
Dim i As Long
i = 2
Set xlWorkbook = Workbooks.Open(“D:\UiPath Projects\Macros\Test.xlsx”, UpdateLinks:=False)
Set xlWorksheet = xlWorkbook.Worksheets(“Sheet1”)
lastUsedRow = xlWorksheet.Cells(Rows.Count, 1).End(xlUp).Row
Set xlRange = xlWorksheet.Range(“A1:A” & lastUsedRow)
For Counter = 2 To xlRange.Rows.Count
'Column Name
column1 = xlRange.Cells(i, 1).Value
If (column1 = “”) Then
i = i + 1
End If
End Function

Hi @Udesheyy

Please try the below method.

(From d In dtData.AsEnumerable Where Not d.ItemArray.Any(Function (x) If(isNothing(x), True, String.IsNullOrWhiteSpace(x.toString.trim))) Select d).CopyToDataTable


HI @Udesheyy ,

Here is an activity to delete only the empty rows.



wow, I am always fascinated how many solutions there are for a problem :stuck_out_tongue_winking_eye:

I also still come up with a

  1. Read Range → dt_var
  2. Filter dt_var → Select in Wizard
    • Remove
    • Column 0
    • isEmpty
  3. Input and Output is dt_var
  4. Write Range