issue with using range that I have obtained

[Oberon70]

Hi,

I keep receiving the below message:

for the below macro

Sub Delete_Last_2_Rows()

Dim i As Long
Dim rng_1 As Range
Dim rng_2 As Range
Dim ws As Worksheet
Set ws = Sheets("Data")
Set rng_1 = Range("A:A")

ws.Select

i = WorksheetFunction.CountA(rng_1) + 2

'MsgBox "A" & i

'MsgBox FindLast(3)

'MsgBox "A" & i & ":" & FindLast(3)

Set rng_2 = ws.Range("A" & i & ":" & FindLast(3))

ws.Rows(rng_2).Select
    Selection.Delete Shift:=xlUp

End Sub

FindLast runs a macro that obtains the last cell for the last row with data in it.

The below is A & the variable i combined

The below is the results from FindLast(3)

when I run MsgBox rng_2.address

I appreciate any help.

 

[Oberon70]

Sorry, editing this post

 

[Joe4]

Is your goal simply to delete the last two lines?
If so, most of this code is unnecessary.

If column B always has data in it, you could just use something like this:

Sub MyDeleteRows()

    Dim lr As Long
   
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
   
'   Delete rows
    Rows(lr - 1 & ":" & lr).Delete
   
End Sub

 

[Oberon70]

Okay, I couldn't sleep, so I have rebooted my computer and added the mini-sheet addon to excel. As per above, I am trying to automate the manual process of me sorting through over 20 statements a month. This is basically, just having the information we need to receipt.

The steps:

1. Open a workbook that I will disable from being saved.
2. The workbook will have a few buttons to choose from, but at this stage, there is only one button, which will activate a macro.

VBA Project - Dummy File (VBA Code Processed).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
2
3
4
5
6
7
8
9
10
11
12
13
14
Sheet1

3. The macro will open a box so that I or one of my co-workers is able to select the statement we want to copy the "Data" sheet from.

The code splits the "data" sheet over sheets; Data_Statement_Details and Data. At this stage I am obtaining information from the sheet Data.

VBA Project - Dummy File (VBA Code Processed).xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	STRPAYDAMT	STRPAYTAMT	STRCOMAMT	STROTHCHAMT	STRGSTAMT	STRCHCRAMT	STRREFAMT	STRTTYCODE	STRCHGCAT	FILCODE	TRNSRVCHAMT	TRNSOLCHAMT	TRNCRTCHAMT	TRNNOTSRVSOLCRTCHAMT	TTYCAT	TRNGSTEXEMPTAMT	FINADDINFO_3	FILCLIREF2
2	0	-50	-35	0	-3.5			PAYT	OTH	17120077	0	0	0	0	810	0
3	0	50	35	0	3.5			PAYT	OTH	17060560	0	0	0	0	810	0		and legal expenses
4	0	10	7	0	0.7			PAYT	OTH	17080859	0	0	0	0	810	0
5	0	2,020.00	1,414.00	0	141.4			PAYT	OTH	83640	0	0	0	0	810	0		217447
6	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
7	0	25	17.5	0	1.75			PAYT	OTH	548903	0	0	0	0	810	0
8	0	20	14	0	1.4			PAYT	OTH	17080647	0	0	0	0	810	0
9	0	4.82	3.37	0	0.34			PAYT	OTH	546308	0	0	0	0	810	0
10	0	424.96	297.47	0	29.75			PAYT	OTH	17090576	0	0	0	0	810	0
11	0	10	7	0	0.7			PAYT	OTH	548634	0	0	0	0	810	0
12	0	10	7	0	0.7			PAYT	OTH	550785	0	0	0	0	810	0
13	0	20	14	0	1.4			PAYT	OTH	552689	0	0	0	0	810	0
14	0	500	350	0	35			PAYT	OTH	17090379	0	0	0	0	810	0
15	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
16	0	5	3.5	0	0.35			PAYT	OTH	17070234	0	0	0	0	810	0
17	0	10	7	0	0.7			PAYT	OTH	17110344	0	0	0	0	810	0
18	0	10	7	0	0.7			PAYT	OTH	550785	0	0	0	0	810	0
19	0	50	35	0	3.5			PAYT	OTH	17060560	0	0	0	0	810	0		and legal expenses
20	0	100	70	0	7			PAYT	OTH	17070030	0	0	0	0	810	0
21	0	10	7	0	0.7			PAYT	OTH	17080859	0	0	0	0	810	0
22	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
23	0	10	7	0	0.7			PAYT	OTH	17110344	0	0	0	0	810	0
24	0	25	17.5	0	1.75			PAYT	OTH	548903	0	0	0	0	810	0
25	0	20	14	0	1.4			PAYT	OTH	17080647	0	0	0	0	810	0
26	0	10	7	0	0.7			PAYT	OTH	550785	0	0	0	0	810	0
27	0	10	7	0	0.7			PAYT	OTH	549616	0	0	0	0	810	0
28	0	3.02	2.11	0	0.21			PAYT	OTH	555922	0	0	0	0	810	0
29	0	51.76	36.23	0	3.62			PAYT	OTH	543964	0	0	0	0	810	0
30	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
31	0	20	14	0	1.4			PAYT	OTH	552689	0	0	0	0	810	0
32	0	5	3.5	0	0.35			PAYT	OTH	17070234	0	0	0	0	810	0
33	0	10	7	0	0.7			PAYT	OTH	17080859	0	0	0	0	810	0
34	0	10	7	0	0.7			PAYT	OTH	550785	0	0	0	0	810	0
35	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
36	0	50	35	0	3.5			PAYT	OTH	17060560	0	0	0	0	810	0		and legal expenses
37	0		0	0	0	2,698.50		CLCC			0	0	0	0	610	0
38	0		0	0	0	0	806.06	TRRF			0	0	0	0	900	806.06
data

4. I want to delete the last two rows or not have them included in the dynamic range. The range determined by the code is assigned to a Defined Name.
5. The code then copies a Column from the range A2 and the last row determined by the code. A new sheet is created called receipted and it is copied here from A2.
6. I will add a reference to each column.

VBA Project - Dummy File (VBA Code Processed).xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	STRPAYDAMT	STRPAYTAMT	STRCOMAMT	STROTHCHAMT	STRGSTAMT	STRCHCRAMT	STRREFAMT	STRTTYCODE	STRCHGCAT	FILCODE	TRNSRVCHAMT	TRNSOLCHAMT	TRNCRTCHAMT	TRNNOTSRVSOLCRTCHAMT	TTYCAT	TRNGSTEXEMPTAMT	FINADDINFO_3	FILCLIREF2
2	0	-50	-35	0	-3.5			PAYT	OTH	17120077	0	0	0	0	810	0
3	0	50	35	0	3.5			PAYT	OTH	17060560	0	0	0	0	810	0		and legal expenses
4	0	10	7	0	0.7			PAYT	OTH	17080859	0	0	0	0	810	0
5	0	2,020.00	1,414.00	0	141.4			PAYT	OTH	83640	0	0	0	0	810	0		217447
6	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
7	0	25	17.5	0	1.75			PAYT	OTH	548903	0	0	0	0	810	0
8	0	20	14	0	1.4			PAYT	OTH	17080647	0	0	0	0	810	0
9	0	4.82	3.37	0	0.34			PAYT	OTH	546308	0	0	0	0	810	0
10	0	424.96	297.47	0	29.75			PAYT	OTH	17090576	0	0	0	0	810	0
11	0	10	7	0	0.7			PAYT	OTH	548634	0	0	0	0	810	0
12	0	10	7	0	0.7			PAYT	OTH	550785	0	0	0	0	810	0
13	0	20	14	0	1.4			PAYT	OTH	552689	0	0	0	0	810	0
14	0	500	350	0	35			PAYT	OTH	17090379	0	0	0	0	810	0
15	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
16	0	5	3.5	0	0.35			PAYT	OTH	17070234	0	0	0	0	810	0
17	0	10	7	0	0.7			PAYT	OTH	17110344	0	0	0	0	810	0
18	0	10	7	0	0.7			PAYT	OTH	550785	0	0	0	0	810	0
19	0	50	35	0	3.5			PAYT	OTH	17060560	0	0	0	0	810	0		and legal expenses
20	0	100	70	0	7			PAYT	OTH	17070030	0	0	0	0	810	0
21	0	10	7	0	0.7			PAYT	OTH	17080859	0	0	0	0	810	0
22	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
23	0	10	7	0	0.7			PAYT	OTH	17110344	0	0	0	0	810	0
24	0	25	17.5	0	1.75			PAYT	OTH	548903	0	0	0	0	810	0
25	0	20	14	0	1.4			PAYT	OTH	17080647	0	0	0	0	810	0
26	0	10	7	0	0.7			PAYT	OTH	550785	0	0	0	0	810	0
27	0	10	7	0	0.7			PAYT	OTH	549616	0	0	0	0	810	0
28	0	3.02	2.11	0	0.21			PAYT	OTH	555922	0	0	0	0	810	0
29	0	51.76	36.23	0	3.62			PAYT	OTH	543964	0	0	0	0	810	0
30	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
31	0	20	14	0	1.4			PAYT	OTH	552689	0	0	0	0	810	0
32	0	5	3.5	0	0.35			PAYT	OTH	17070234	0	0	0	0	810	0
33	0	10	7	0	0.7			PAYT	OTH	17080859	0	0	0	0	810	0
34	0	10	7	0	0.7			PAYT	OTH	550785	0	0	0	0	810	0
35	0	10	7	0	0.7			PAYT	OTH	543460	0	0	0	0	810	0
36	0	50	35	0	3.5			PAYT	OTH	17060560	0	0	0	0	810	0		and legal expenses
37	0		0	0	0	2,698.50		CLCC			0	0	0	0	610	0
38	0		0	0	0	0	806.06	TRRF			0	0	0	0	900	806.06
data

7. The macro then adds the formula Vlookup to the Sheet "Receipt"
8. The next step is to consolidate the number of transactions on the statement. A lot of the transactions are a couple of payments made by the same client. (This part I am really stuck on as I haven't figured out how to code this yet.

VBA Project - Dummy File (VBA Code Processed).xlsm
	A	B	C	D
2	295678410	-$50.00	-$38.50	-$11.50
3	394308240	$50.00	$38.50	$11.50
4	889035494	$10.00	$7.70	$2.30
5	939394939	$2,020.00	$1,555.40	$464.60
6	848593874	$10.00	$7.70	$2.30
7	239394838	$25.00	$19.25	$5.75
8	38299999	$20.00	$15.40	$4.60
9	675768476	$4.82	$3.71	$1.11
10	545496854	$424.96	$327.22	$97.74
11	111111111	$10.00	$7.70	$2.30
12	545485848	$10.00	$7.70	$2.30
13	485848584	$20.00	$15.40	$4.60
14	340145338	$500.00	$385.00	$115.00
15	848593874	$10.00	$7.70	$2.30
16	222222222	$5.00	$3.85	$1.15
17	768576757	$10.00	$7.70	$2.30
18	545485848	$10.00	$7.70	$2.30
19	394308240	$50.00	$38.50	$11.50
20	99495848	$100.00	$77.00	$23.00
21	889035494	$10.00	$7.70	$2.30
22	848593874	$10.00	$7.70	$2.30
23	768576757	$10.00	$7.70	$2.30
24	239394838	$25.00	$19.25	$5.75
25	38299999	$20.00	$15.40	$4.60
26	545485848	$10.00	$7.70	$2.30
27	909945984	$10.00	$7.70	$2.30
28	359489584	$3.02	$2.32	$0.70
29	878574757	$51.76	$39.85	$11.91
30	848593874	$10.00	$7.70	$2.30
31	485848584	$20.00	$15.40	$4.60
32	222222222	$5.00	$3.85	$1.15
33	889035494	$10.00	$7.70	$2.30
34	545485848	$10.00	$7.70	$2.30
35	848593874	$10.00	$7.70	$2.30
36	394308240	$50.00	$38.50	$11.50
Receipt

Cell Formulas
Range	Formula
B2:B36	B2	=VLOOKUP(A2,Statement_Data,6,FALSE)
C2:C36	C2	=VLOOKUP(A2,Statement_Data,7,FALSE) + VLOOKUP(A2,Statement_Data,9,FALSE)
D2:D36	D2	=B2-C2

Named Ranges
Name	Refers To	Cells
Statement_Data	=data!$A$2:$V$38	B2:C36

8. Once the data is consolidated, I delete all the rows from the 1st empty row of the consolidated data and copy and paste special the consolidated Data from A2. I then place the remaining information in a form for receipting. The goal is one the Data is copied from the statement then I end up with the below and a new workbook is created with the below.

VBA Project - Dummy File (VBA Code Processed).xlsm
	A	B	C	D	E	F	G	H
1	Claim Number	Payment	Inc Comm	Net to QBE	Inv Number	Batch Num	Date Receipted	Receipt Number
2	038299999	$40.00	$30.80	$9.20	7060
3	099495848	$100.00	$77.00	$23.00	7060
4	111111111	$10.00	$7.70	$2.30	7060
5	222222222	$10.00	$7.70	$2.30	7060
6	239394838	$50.00	$38.50	$11.50	7060
7	295678410	-$50.00	-$38.50	-$11.50	7060
8	340145338	$500.00	$385.00	$115.00	7060
9	359489584	$3.02	$2.32	$0.70	7060
10	394308240	$150.00	$115.50	$34.50	7060
11	485848584	$40.00	$30.80	$9.20	7060
12	545485848	$40.00	$30.80	$9.20	7060
13	545496854	$424.96	$327.22	$97.74	7060
14	675768476	$4.82	$3.71	$1.11	7060
15	768576757	$20.00	$15.40	$4.60	7060
16	848593874	$50.00	$38.50	$11.50	7060
17	878574757	$51.76	$39.85	$11.91	7060
18	889035494	$30.00	$23.10	$6.90	7060
19	909945984	$10.00	$7.70	$2.30	7060
20	939394939	$2,020.00	$1,555.40	$464.60	7060
21		$3,504.56	$2,698.50	$806.06
Goal

Cell Formulas
Range	Formula
E2:E20	E2	=Data_Statement_Details!$B$37
B21:D21	B21	=SUM(B2:B20)

I am not sure if the code I pasted above will show up in what I have entered.

 

[Joe4]

That is asking much more than was implied in your first post.
Your original question seem focused on one particular issue, not the whole entire project.
I was willing to try to help out on that one particular issue you originally asked about, but probably don't have time to commit myself to helping with the larger project.

 

[Oberon70]

All good, I figured out the issue with deleting the two rows. I have everything else working accept for the consolidating the data. Sorry, I didn’t mean to imply that I needed help on the whole thing. I am just the type of person that explain what my end goal is and when I posted this post it was deleting the two rows.

 

[Joe4]

I have everything else working accept for the consolidating the data.

Glad to hear that you got that part working.
Since the data consolidation is different than your original question, if you would like help with that, it may be best to post that as its own new question.
That way it will appear in the "Unanswered threads", increaing the likelihood of responses and help.

 

[Oberon70]

Thank you for all the help, I was able to Delete the last two rows