Delete empty rows in excel expect headers using vba

Help Studio
, , , , ,
1

Hi everyone,

My requirement is to delete empty/blank rows from excel except headers , i used insert/delete rows , but it takes more time to delete if 1000’s of records , so i tried an alternate of invoke vba with clear contents , but that adds blanks spaces , so i need delete blank rows in excel with vba any solutions
I am trying to use the below code but getting some errors macros disable when it is actually enabled , but idk how to use this code and pass inputs in invoke vba
@Rahul_Unnikrishnan

Sub DeleteExceptFirst()
Rows("2:" & Rows.Count).ClearContents
End Sub

@Palaniyappan @Yoichi @supermanPunch
Thanks in advance

2

You could read the excel into a datatable and use LinQ to filter then rewrite the datatable?

Use Invoke Code:
Code =

DataTable.AsEnumerable().Where(Function(row) row.ItemArray.All(Function(field) field Is Nothing Or field Is DBNull.Value Or field.Equals(“”))).ToList().ForEach(Sub(row) row.Delete())

Argument =

Datatable - In/Out

1 Like
3

Dt.ASenumerable.where(Function(r) Dt.Columns.Cast(of DataColumn).All(Function(c) Not String.isNullorEmpty(r(c).Tostring.Trim))).Copytodatatable

1 Like
4

@Gangadhar_Athili should be used in assign activity?

5

Yes - Assign it to datatable variable

1 Like
6

I got this
Assign: The source contains no DataRows.

7

Share DT Data Once

8

Make sure you replace DT with the variable name of your datatable.