Delete empty rows (Position will be change)

Help Studio
, ,
1

Hi,

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.

2

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
xlRange.Cells(i).EntireRow.Delete
Else
i = i + 1
End If
Next
xlWorkbook.Save
xlWorkbook.Close
End Function

3

Hi @Udesheyy

Please try the below method.

image
image1379×548 34 KB

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

Regards

4

HI @Udesheyy ,

Here is an activity to delete only the empty rows.

Regards
Balamurugan.S

5

Hi

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

:grinning:

1 Like