Deleting Blank Rows (VBA)

khawarameer · Jun 29, 2010

Dear All,

I have a large worksheet with approx 55600 rows. However many rows in the sheet are empty. I used a macro to delete empty rows but it is giving me "Run time error '6' ". Here is the code i am using

Sub Deleterow()

Dim xlastrow As Integer
Dim xrow As Integer
xrow = 1

Range("a65000").End(xlUp).Select

xlastrow = ActiveCell.Row

Do Until xrow = xlastrow

If Cells(xrow, 1).Value = "" Then
Cells(xrow, 1).Select
Selection.EntireRow.Delete

xrow = xrow - 1
xlastrow = xlastrow - 1

End If

xrow = xrow + 1

Loop
End Sub

Can any one help me with this.

Regards

Khawar Ameer Malik

khawarameer · Jun 29, 2010

VoG said:
I have tested it. It only deletes blanks.

Sir, Its not working on mine. Maybe i am not running it right. Is there a technique to run the code i mean which cell should i select first and then run the code. There must be a mistake somewhere.

Tom Urtis · Jun 29, 2010

Hi Colin - -

Are not visible rows still Special and, if exceeding the 8192 limit, require some extra work even if filtered, or am I missing something.

Tom Urtis · Jun 29, 2010

khawarameer said:
Sir, Its not working on mine. Maybe i am not running it right. Is there a technique to run the code i mean which cell should i select first and then run the code. There must be a mistake somewhere.

Did you read what I posted, and what is the count of blanks.

yogeshmaney · Jun 29, 2010

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CYOGESH%7E1.MAN%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CYOGESH%7E1.MAN%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CYOGESH%7E1.MAN%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Sub REMOVE_BLANKS()<o

></o

>
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete<o

></o

>
End Sub<o

></o

>

Colin Legg · Jun 29, 2010

Tom Urtis said:
Hi Colin - -

Are not visible rows still Special and, if exceeding the 8192 limit, require some extra work even if filtered, or am I missing something.

Hi Tom -- I'm saying not to use special cells but to use the mouse/keyboard to manually select the filtered (visible) rows. It should be a 5 second job.

Of course, in XL 2010 the special cells limit is no longer an issue.

khawarameer · Jun 29, 2010

Tom Urtis said:
Did you read what I posted, and what is the count of blanks.

Well Tom,

I am unable to count blank row as "The Selection is too large" error occurs. however i thing they are no less than 10,000, further there are no hidden rows i my sheet.

khawarameer · Jun 29, 2010

yogeshmaney said:
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CYOGESH%7E1.MAN%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CYOGESH%7E1.MAN%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CYOGESH%7E1.MAN%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style>
Sub REMOVE_BLANKS()<o></o>
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete<o></o>
End Sub<o></o>

Thanks yogeshmaney,

It worked.

Best regards

Tom Urtis · Jun 29, 2010

...deleted

Deleting Blank Rows (VBA)

khawarameer

Board Regular

khawarameer

Board Regular

Tom Urtis

MrExcel MVP

Tom Urtis

MrExcel MVP

yogeshmaney

Active Member

Colin Legg

MrExcel MVP

khawarameer

Board Regular

khawarameer

Board Regular

Tom Urtis

MrExcel MVP

Similar threads

Share this page

Deleting Blank Rows (VBA)

Board Regular

Board Regular

MrExcel MVP

MrExcel MVP

Active Member

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock