VBA code to run till the last row with value

[RAJESH1960]

Hello Guys,
In the code, the formula is copied from G1 & H1 and when I double click it, the line fills the formula till the G1:H22 range which is correct in this case. But the range could be different in other cases. When I copy the whole range and paste it below the data the formula is again calculated till G1:H22 which is not right. How do I change the code so that it will work till the last value of the cell?

Option Explicit

Sub AutoFill()
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",-RC[2])"
Range("H1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[2]="""","""",RC[2])"
Range("G1:H1").Select
Selection.AutoFill Destination:=Range("G1:H23")
Range("G1:H22").Select
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J
1	1	30-06-2021	Receipt	100		(as per details)			4040
2	2	30-06-2021	Receipt	100		Sunday				1015.00
3	3	30-06-2021	Receipt	100		Monday				2025.00
4	4	30-06-2021	Receipt	100		Tuesday				1000.50
5	5	30-06-2021	Receipt	100		Round Off			1
6	8	04-07-2021	Payment	103		(as per details)				1001.00
7	9	04-07-2021	Payment	103		January			100
8	10	04-07-2021	Payment	103		February			200
9	11	04-07-2021	Payment	103		March			300
10	12	04-07-2021	Payment	103		April			400
11	13	04-07-2021	Payment	103		Round Off			1
12	14	05-07-2021	Receipt	104		(as per details)			4040
13	15	05-07-2021	Receipt	104		Sunday				1015.00
14	16	05-07-2021	Receipt	104		Monday				2025.00
15	17	05-07-2021	Receipt	104		Tuesday				1000.50
16	18	05-07-2021	Receipt	104		Round Off			1
17	21	09-07-2021	Payment	107		(as per details)				1001.00
18	22	09-07-2021	Payment	107		January			100
19	23	09-07-2021	Payment	107		February			200
20	24	09-07-2021	Payment	107		March			300
21	25	09-07-2021	Payment	107		April			400
22	26	09-07-2021	Payment	107		Round Off			1
A

 

[RAJESH1960]

If someone can please explain the code in a easy way it would be helpful as there are 3 more places in the code where I need to select selected rows and columns containing data.

 

[GWteB]

Assuming that column A determines the last filled row, you could try the following.

Sub AutoFill()
    Range("G1").FormulaR1C1 = "=IF(RC[2]="""","""",-RC[2])"
    Range("H1").FormulaR1C1 = "=IF(RC[2]="""","""",RC[2])"
    Range("G1:H1").AutoFill Destination:=Range("G1:H" & Cells(Rows.Count, "A").End(xlUp).Row)
End Sub

 

[Fluff]

How about

   With Range("G1:G" & Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=IF(RC[2]="""","""",-RC[2])"
      .Offset(, 1).FormulaR1C1 = "=IF(RC[2]="""","""",RC[2])"
   End With

 

[RAJESH1960]

Assuming that column A determines the last filled row, you could try the following.

Sub AutoFill()
    Range("G1").FormulaR1C1 = "=IF(RC[2]="""","""",-RC[2])"
    Range("H1").FormulaR1C1 = "=IF(RC[2]="""","""",RC[2])"
    Range("G1:H1").AutoFill Destination:=Range("G1:H" & Cells(Rows.Count, "A").End(xlUp).Row)
End Sub

That worked perfectly in 2 different sheets. Thanks GWteb

 

[GWteB]

You are welcome. Glad we could help.

 

[RAJESH1960]

How about

   With Range("G1:G" & Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=IF(RC[2]="""","""",-RC[2])"
      .Offset(, 1).FormulaR1C1 = "=IF(RC[2]="""","""",RC[2])"
   End With

Thanks Fluff.

 

[Fluff]

You're welcome.