Delete empty rows

[nutjob]

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?

Thanking you in anticipation.

 

[EB08]

Something like this might work out for you...

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]

Not sure, you're up to this.

It deletes emp'ty rows in collumn A

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]

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?

Thanking you in anticipation.

With minimal modification to your existing setup, try replacing the Loop line with

Loop Until ActiveCell.End(xldown).Row  = RowsCount

 

[nutjob]

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