UiPath Remove or Delete Empty Rows Example |Video upload date:  · Duration: PT3M47S  · Language: EN

Quick UiPath guide to find and delete empty Excel rows using DataTable filtering and activities for cleaner automation workflows

If your UiPath automation chokes on blank lines in Excel you are not alone. Empty rows are the little gremlins of RPA they hide in spreadsheets and break your happy flow. This short tutorial shows reliable ways to detect and remove empty rows from an Excel sheet using DataTable operations and core UiPath activities so your automation runs like it was fed coffee.

Quick steps summary

  • Prepare a project and place the sample Excel file in a known folder
  • Read the sheet into a DataTable using Read Range
  • Remove empty rows with DataTable filters or with a bottom up delete loop
  • Write the cleaned DataTable back with Write Range and AutoFit if you care about looks
  • Test on sample data and save the process

Read workbook into a DataTable

Use Read Range from the Excel or Workbook package and store the result in a variable named dt or any sensible name that future you will thank you for. Make sure the spreadsheet has named columns or at least predictable headers. Working with a DataTable is cleaner than string parsing drama.

Filter empty rows using DataTable methods

The cleanest approach is to use DataTable filtering and then CopyToDataTable. If one column defines whether a row is empty use a Select expression and replace the original table with the filtered result.

Single column example

dt = dt.Select("Not (ColumnName Is Null Or ColumnName = '')").CopyToDataTable()

This keeps rows where ColumnName is not null and not empty. It is fast and elegant when one column determines whether a row is meaningful.

Multiple columns example

If emptiness means several columns are blank build a combined filter. For example if ColA and ColB both must be blank to count as an empty row use an expression like this and then CopyToDataTable.

dt = dt.Select("(Not (ColA Is Null Or ColA = '')) Or (Not (ColB Is Null Or ColB = ''))").CopyToDataTable()

If you need to consider a row empty only when all relevant fields are blank build the expression with And instead of Or. If CopyToDataTable fails because no rows match wrap the logic with a check on the result length to avoid runtime exceptions.

Alternative method using a delete loop

Sometimes filters are awkward for mixed types or complex blank rules. In that case use a loop that deletes rows from the bottom up. Deleting from bottom to top avoids skipping rows when indexes shift. This works both inside a DataTable loop or by using Delete Range or Delete Row activities against the Excel sheet.

  • Get the row count with dt.Rows.Count
  • Loop i from dt.Rows.Count - 1 down to 0
  • Check the relevant columns on dt.Rows(i) and call dt.Rows.RemoveAt(i) when the row is empty
  • When deleting in Excel use the Delete Row or Delete Range activity targeting the sheet and use the Excel row number that matches your DataTable index plus header offset

Tracking indexes avoids the classic off by one chaos and keeps your automation deterministic. This approach is more code heavy but gives fine control for edge cases.

Write cleaned DataTable back to Excel

Use Write Range to overwrite the original sheet or write to a new sheet if you want a rollback. If presentation matters use AutoFit on the range after writing because stakeholders will judge success by neat columns.

Tips and gotchas

  • Test with a copy of the file before touching production spreadsheets
  • When using Select and CopyToDataTable handle the case where no rows match to avoid exceptions
  • Prefer readable column names to avoid cryptic expressions in the filter
  • Use the Workbook package for file based work and the Excel package when you need Excel specific activities
  • Log the number of rows removed so you can prove the bot did something useful

Fixing empty rows is one of those boring but essential RPA chores. Do it once cleanly and your downstream logic will stop staging dramatic exits. Now go remove those blank rows like the automation hero you are.

I know how you can get Azure Certified, Google Cloud Certified and AWS Certified. It's a cool certification exam simulator site called certificationexams.pro. Check it out, and tell them Cameron sent ya!

This is a dedicated watch page for a single video.