How to delete empty rows

[Raaz74]

I want to create a macro that delete all empty rows and bring rows up.

Sub delete()

Rows("2:2, 5:5").Select
Selection.delete Shift:=xlUp

End Sub

I recorded a macro but it is only selecting specified rows e.g 2 and 5 and ofcourse if i run this macro again then it deletes value from 2 and 5 again. What i need is if my excel sheet finds any empty rows, macro deletes them and bring rows up if there is no empty rows in whole sheet then it should not do anything. Any suggestion ??

-Thank you,
Raaz

[Gintaras]

Macro Excel delete empty rows

Code:

Copy      Help

ExcelSheet es.Init ;;connect to Excel
ARRAY(str) a; es.CellsToArray(a "<all>")
;delete starting from bottom
int r c nc=a.len(1)
for r a.len-1 -1 -1
,for(c 0 nc) if(a[c r].len) break
,if(c<nc) continue
,Excel.Range R=es.ws.Rows.Item(r+1)
,R.Delete(Excel.xlUp)

[Raaz74]

Thank you! but i am really new for macros. This is what i am doing, in Excel i put some values in column "A" to check your code, went to tools -->macros and type exactly the following code but it is giving me syntax error macro name is "deletenew"

Sub deletenew()
'/exe 1
'ExcelSheet es.Init ;;connect to Excel

ARRAY(int) ad
'rows to delete
ARRAY(str) ar
'row cells
int i n row

'for each row in used range
Row = es.ws.UsedRange.Row
foreach ar "" FE_ExcelRow
'is empty?
for(i 0 ar.len) if(ar[i].len) break
if(i=ar.len) ad[]=row ;;empty
Row 1

;delete starting from bottom
for i ad.len-1 -1 -1
Excel.Range r=es.ws.Rows.Item(ad[i])
r.Delete (Excel.xlUp)

End Sub

Do you know what is wrong ?

Thank you,
-Raaz

[Gintaras]

Type the code in Quick Macros, not in Excel.

[Raaz74]

Thanks again, I can not install quick macro on my work laptop. Do you have a code for Excel ?

-Raaz

[Gintaras]

On a computer where QM is installed, you can convert this macro to exe, and run the exe on other computers.

[Raaz74]

Thanks, i will install QM on my personal laptop and put the exe file on work laptop. I will be copying the same code mentioned below in my QM, please correct if this code looks wrong.

;/exe 1
ExcelSheet es.Init ;;connect to Excel

ARRAY(int) ad ;;rows to delete
ARRAY(str) ar ;;row cells
int i n row

;for each row in used range
row=es.ws.UsedRange.Row
foreach ar "" FE_ExcelRow
,;is empty?
,for(i 0 ar.len) if(ar[i].len) break
,if(i=ar.len) ad[]=row ;;empty
,row+1

;delete starting from bottom
for i ad.len-1 -1 -1
,Excel.Range r=es.ws.Rows.Item(ad[i])
,r.Delete(Excel.xlUp)

-Raaz

[pedrotairum]

Hey Gintaras,

This code skips the first rows of the file. If the file has the 1st and 2nd row empty and starts on the 3rd, this code only starts counting from row number 3.
How can I make it check all the rows?

Thanks mate!

[Gintaras]

Updated.

[pedrotairum]

Works like a charm.

Thanks!