Insert blank line in Excel after certain criteria using VB. 2

[gj0519]

I am exporting data from Access into Excel using VB. What I would like to do is after a certain record has been passed into Excel is to insert a blank line before the next record. Not sure how to do this.
Thanks,
gj0519

 

[THogan]

Code to insert a blank row in excel from vb:

ActiveCell.EntireRow.Insert

 

[SkipVought]

gj0519,

Just be careful using Insert and Delete when columns/rows/cells are SHIFTED.

Here's why. If you reference cells on that sheet, your references move. That may or may not be what you want to happen.

Ususlly, when "inserting" data, it is customary to "insert" at the BOTTOM of the table/list and then after all the "inserts" have been performed, SORT the data by the table keys.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gj0519]

I am able to insert a new row, where the cell is active.
I have 700 rows of data and it changes daily, I need to insert rows at specific places without having to manualy looking. Here is a small piece of my code where I am trying to do this.FISC is my column name with numbers up to 900. I am trying to tell it is when the last row with 700 is found, then insert a row after that record.
Thanks,
gj0519

Private Sub OrganizeForm_Click()
Dim FISC As Integer
If FISC < 700 Then
ActiveCell.EntireRow.Insert
End If

End Sub

 

[THogan]

Private Sub CommandButton1_Click()
Dim lngValue As Long

Range(&quot;A1&quot.Select 'active cell at column heading FISC
lngValue = 0
Do While lngValue <= 700
ActiveCell.Offset(1, 0).Select
lngValue = ActiveCell.Value
Loop
'activecell is now on the first row where value is bigger than 700
ActiveCell.EntireRow.Insert
End Sub

 

[THogan]

Of course - you'll need to add your own error handling and you might want to turn off screen updating while its processing...

 

[gj0519]

Thanks,
Still having some problems though. I have
Range(&quot;G4&quot.select
and it inserts a row at G4, if I change the range value, it inserts a row at that point. Not sure what I am doing wrong.
gj0519

 

[THogan]

I've tested my code on the following:

G4 = FISC
G5 = 23
G6 = 45
G7 = 100
G8 = 700
G9 = 701

and it inserts a blank row at G9 (between 700 and 701).

What are you testing it with? Have you copied the code verbatim (and just edited the start cell to G4 from A1)?

 

[duncansancho]

Do you not need to put

Activecell.Offset(1,0).Range(&quot;A1&quot.Select

wherever you start from?

 

[gj0519]

This is crazy, if I open an empty worksheet and enter in test data and change the code to look at Range(&quot;G1&quotI can get it to work. But when I try it with the real data it inserts a row after G1? Thanks a bunch for the help THogan.

gj0519