Runtime error 1004 when inserting rows via VBA

V

vdel.84

New Member
Joined
Jan 19, 2010
Messages
5
Hi,
I am trying to run a simple macro that will insert x number of rows depending upon the value in a cell. Here is the code:

Sub InsertRows()

Dim c As Range
Dim RowCount As Long

For Each c In ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
RowCount = c.Offset(0, 8)
c.Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Rows.Insert.Resize(RowCount).xlShiftDown

Next c

End Sub
Code:


When it gets to the line "Rows.Insert.Resize(RowCount).xlShiftDown", I receive a Run-Time error 1004: Possible loss of data because Excel can't move non-blank cells of the worksheet. Find the last non-blank cell and delete everything in between that and your data set. Select cell A1 and save your worksheet to reset the last cell.


I have done this a few times, and have even closed out of Excel completely after clearing out the cells around my data set. I've tried copying the data into a new sheet, new workbook, etc. I can't see anything wrong with the code. I am able to successfully insert rows manually, just not through VBA. There are only 236 rows of data in my sheet....also, the RowCount value maxes out at 9.

Help...!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
You really should get rid of all that Select/Selection/ActiveCell stuff.

It's not needed and could actually be the root of your problem.

Perhaps if you explained further, perhaps with some sample data, what you want to do some alternative code could be provided.:)
 
Upvote 0
Sorry...here is cleaner code (I think...I'm fairly new at this):

Sub InsertRows()

Dim c As Range
Dim RowCount As Long

For Each c In ActiveSheet.Range("A2", Range("A" & Rows.Count).End(xlUp))
RowCount = c.Offset(0, 8)
c.Offset(1, 0).Range(Selection, Selection.End(xlToRight)).Select
Rows.Insert.Resize(RowCount).xlShiftDown

Next c

End Sub


The data represents invoices for inpatient stays. If a patient had more than one day of service, the units and charges are summed on the same line. My boss wants me to break out one row for every day of service, so I'm using the number of units in the 8th column to determine the number of rows to insert.
 
Upvote 0
I am not sure what is wrong with the code you provided but I pretty much tossed it out and started again. Iterating through a range and inserting rows along the way in a For Each loop has a pitfall. If your variable c is sitting in row 5, and you insert a row above it, the variable will now point to row 6. But For Each doesn't know that you did that, and it decides, "Ok time to iterate from row 5 to row 6." But you were already there. This puts you in an infinite loop.

I rewrote it as a Do While loop.

Also, you were inserting every time, without checking the value NumberRows. I am assuming that sometimes it will be blank or zero.

Also, if you put the code in the code for the sheet, you don't have to qualify it as ActiveSheet.

Here is the code. It should be inserted in the code for the sheet itself (not a Module).

Code: 
Option Explicit
Sub InsertRows()
   Dim c As Range ' iterate through all rows in column A
   Dim RowCount As Long ' Number of rows to insert
   Dim LastCell As Range ' Last cell containing data in column A
 
   Set LastCell = Range("A" & Rows.Count).End(xlUp)
   Set c = ActiveSheet.Range("A2")
 
   Do While c.Address <> LastCell.Address
 
      RowCount = c.Offset(0, 8)
 
      If RowCount > 0 Then
         Rows(c.Row & ":" & c.Row + RowCount - 1).Insert Shift:=xlDown
      End If
      Set c = c.Offset(1, 0)
 
   Loop
 
End Sub

BTW please use CODE tags when putting code in a post. :)
 
Upvote 0
vdel.84 said:
The data represents invoices for inpatient stays. If a patient had more than one day of service, the units and charges are summed on the same line. My boss wants me to break out one row for every day of service, so I'm using the number of units in the 8th column to determine the number of rows to insert.
Click to expand...
Sorry, didn't see this post until after I posted my code. If there's 1 day of service, then you only need 1 row, right? So you need to insert x-1 rows, not x?
 
Upvote 0
Thank you! This works great...is there a way that i can get the rows to be inserted below the selected row instead of above?
 
Upvote 0
Sorry, i just saw your other question.

For example, if a patient stayed for 3 days of service, the line of data will have the value of 3 for units (which is what i'm using for x)

that one row needs to be 3 rows now, one for every day they stayed...so it needs to be x number or rows.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
run-time error '9' subscript out of range
Replies
1
Views
446
NdNoviceHlp
NdNoviceHlp
D
  • Solved
VBA Filter code not working with blank variable criteria
Replies
7
Views
327
kevin9999
K
E
  • Solved
VBA to bypass protected cell (Error #1004)?
Replies
4
Views
245
EssKayKay
E
S
  • Question
Insert Custom Rows VBA - Multiple Worksheets
2
Replies
10
Views
443
Peter_SSs
Peter_SSs
D
  • Solved
Unable to select a cell in a worksheet
Replies
8
Views
210
rlv01
R

Forum statistics

Threads
1,214,911
Messages
6,122,195
Members
449,072
Latest member
DW Draft

Share this page

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
Back
Top