Delete empty rows

nutjob

New Member
Joined
Jun 26, 2012
Messages
4
Hi...

I have a macro that loops through the data from a workbook and inserts into an Oracle Table.

Do
...
Read cell...
insert query...
...
ActiveCell.Offset(1, 0)
Loop Until IsEmpty(ActiveCell.Offset(0, 0))

The problem I am facing is, the spreadsheet the user passes have empty rows in between. for eg: Out of 100 rows, only 50 rows are inserted because 51st row is empty.

How do I avoid this?:confused:

Thanking you in anticipation.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

EB08

Active Member
Joined
Jun 9, 2008
Messages
343
Something like this might work out for you...

Code:
Sub ProcessRecs()
Dim rngRecords As Range
Dim rngRec As Range

Set rngRecords = Columns(1).SpecialCells(xlCellTypeConstants, 23)
For Each rngRec In rngRecords
    'processing logic here...
    MsgBox "Processing Record: " & rngRec.Address
Next rngRec


End Sub

Where you set the range to include on records with data prior to doing the iteration, rather than testing each record coming through for content.
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
Not sure, you're up to this.

It deletes emp'ty rows in collumn A

Code:
Sub delete_empty_rows_in_collumnA()
With Columns("A").SpecialCells(xlCellTypeBlanks).Cells
    .EntireRow.Delete Shift:=xlUp
  End With
  If Err.Number <> 0 Then MsgBox "There are no or only empty cells"
 End Sub
 

mirabeau

Banned user
Joined
Nov 4, 2010
Messages
2,075
Hi...

I have a macro that loops through the data from a workbook and inserts into an Oracle Table.

Do
...
Read cell...
insert query...
...
ActiveCell.Offset(1, 0)
Loop Until IsEmpty(ActiveCell.Offset(0, 0))

The problem I am facing is, the spreadsheet the user passes have empty rows in between. for eg: Out of 100 rows, only 50 rows are inserted because 51st row is empty.

How do I avoid this?:confused:

Thanking you in anticipation.
With minimal modification to your existing setup, try replacing the Loop line with
Code:
Loop Until ActiveCell.End(xldown).Row  = RowsCount
 

nutjob

New Member
Joined
Jun 26, 2012
Messages
4
Thank you all for the help. I made a call to the below sub before processing...

Sub DeleteBlankRows()
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,753
Messages
5,626,665
Members
416,199
Latest member
Gautamsunil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top