Loop thru blanks rows

[jonnj]

Hello all,

I have the following code attached to a command button:

Private Sub CommandButton1_Click()
Dim Number_of_rows As Long

Number_of_rows = ActiveSheet.UsedRange.Rows.Count
Range("c3").Select
Do Until Selection.Row = Number_of_rows + 1
If Selection.Value <> Selection.Offset(-1, 0).Value Then
Selection.EntireRow.Insert
Number_of_rows = Number_of_rows + 1
Selection.Offset(2, 0).Select
Else
Range("c3").Offset(1, 0).Select
End If
Loop

End Sub


This code loops thru column c and inserts a row between each
group of cells with different criteria. However this column may have a blank cell and this code still inserts a blank row.

I would like to amend this code to loop thru blank cells and
just insert a blank row that contains data.

Any suggestions would be greatly appreciated.

Thanks
Jon

 

[SkipVought]

hi,

This sounds interesting...

...insert a blank row that contains data

Is this like dry water?

Please explain what you are trying to accomplish with inserting rows? Are you trying to create space between groups? If so, changing the RowHeight would be a million times better than inserting and empty row.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[jonnj]

Skip,

Let me backtrack. I'm have a userform to enter data into the spreadsheet. It could be from one entry to 200 entries. Im then using the sort(ascending) then the subtotal function.
Once sorted, in column C, entries are made for certain classifications. I wanted to insert a row in between these classications for printing purposes.
This code did this. However since I condensed the subtotal function to show only totals, it creates blank rows in column C that arent seen when condensed.

I hope this explanation is clear enuff.

Thanks
Jon

 

[SkipVought]

I'd advise AGAINST inserting empty rows into a table. That hampers its usefulness as you are experiencing.

Rather, change the row height or use other FORMATTING for visual effect.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[jonnj]

Skip,

Thanks

.. I 'll try the row height.

John

 

[xlbo]

Darn tooting - a lot of functionality is lost or unavailable if you break up a nice contiguous table !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff, you read my mind!

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}