Insert Rows in Excel from ms Access

abenitez77

Board Regular
Joined
Dec 30, 2004
Messages
149
I have this data below and I manually insert 2 empty rows according to the criteria below. On the first empty row I sum up the netclaim according to the criteria being met. I want to be able to do this part using VBA. I insert this data from ms access using this line: loWorkSheet.Range("A4").CopyFromRecordset rs2

This is my criteria:
SentDt <= 30 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >30 AND <= 60 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >30 AND <= 60 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >60 AND <= 90 Then insert 2 empty rows and sum netclaim for this dataset above
SentDt >90 Then insert 2 empty rows and sum netclaim for this dataset above

* I am using DateDiff to determine the number of days from SentDt compared to GetDate() (IN SQL Server)
i.e. DateDiff(day, Cast([SentToVendorDate] as Date), GetDate()) <= 30 THEN 30

(see data sample below)


DatenetClaimSentDt
22-Apr-22$18,926.0011-May-22
27-Apr-22$25,011.0011-May-22
04-May-22$1,142.1926-May-22
12-May-22$3,651.8526-May-22
19-May-22$4,759.0026-May-22
06-May-22$5,771.5726-May-22
06-May-22$10,198.4126-May-22
31-60 Days$69,460.02
28-Apr-22$105,775.0106-Jun-22
07-Jun-22$1,947.0015-Jun-22
07-Jun-22$2,000.0015-Jun-22
10-Jun-22$2,000.0015-Jun-22
10-Jun-22$7,246.0015-Jun-22
15-Jun-22$3,132.6422-Jun-22
16-Jun-22$5,699.4622-Jun-22
16-Jun-22$7,168.5022-Jun-22
15-Jun-22$8,670.3422-Jun-22
15-Jun-22$16,013.8322-Jun-22
16-Jun-22$18,336.3022-Jun-22
0-30 Days
$177,989.08
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Perhaps use Select Case block and insert accordingly, using Excel automation
VBA Code:
Function testSelect(SentDt As Long)
Select Case True
  Case SentDt <= 30
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 30 And SentDt <= 60
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 30 And SentDt <= 60
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 60 And SentDt <= 90
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case Is > 90
     'code to insert 2 empty rows and sum netclaim for this dataset above
End Select

End Function
 
Last edited:
Upvote 0
Perhaps use Select Case block and insert accordingly, using Excel automation
VBA Code:
Function testSelect(SentDt As Long)
Select Case True
  Case SentDt <= 30
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 30 And SentDt <= 60
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 30 And SentDt <= 60
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case SentDt > 60 And SentDt <= 90
     'code to insert 2 empty rows and sum netclaim for this dataset above
  Case Is > 90
     'code to insert 2 empty rows and sum netclaim for this dataset above
End Select

End Function
This part is clear for me...I was having issues with the actual syntax for looping thru rows in the excel object (sheet).
 
Upvote 0
There must be a gazillion posts out there about how to do that. Assuming the column in question is A and the 1st row is a header, then

For Each rng In Range("A2", Cells(Rows.count, "A").End(xlUp))
 
Upvote 0
There must be a gazillion posts out there about how to do that. Assuming the column in question is A and the 1st row is a header, then

For Each rng In Range("A2", Cells(Rows.count, "A").End(xlUp))
gazillion and 1 now. Thanks!
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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