VBA Select all rows from active cell (selected cell) on table to 2nd last row

[willow1985]

I am looking for the most efficient way to select all rows of data from a table from a selected cell to the 2nd last row on a table.

Copy the data and re-paste it as values.

The reason for this is on the original spreadsheet there are a lot of formulas to assist with data entry that bog down the sheet when too much data is entered.
So after an individual keys in the spreadsheet all they would have to do is select the first cell they started keying and run the macro and it would copy all rows from the selection down to the 2nd last row in the table and convert the data to values, clearing the formulas.
I want to leave the last row however to preserve the formulas for when data has to be keyed again.

Example: if cell A21 was selected, the macro would select rows 21 down to row 43, copy and paste values but leave row 44 intact.
Note: the real spreadsheet has a lot more data and formulas.

Thank you to anyone who can help me!

Book1
	A	B	C	D	E	F	G
1	OrderDate	Region	Rep	Item	Units	UnitCost	Total
2	1/6/2020	East	Jones	Pencil	95	1.99	189.05
3	1/23/2020	Central	Kivell	Binder	50	19.99	999.5
4	2/9/2020	Central	Kivell	Pencil	36	4.99	179.64
5	2/26/2020	Central	Kivell	Pen	27	19.99	539.73
6	3/15/2020	West	Jardine	Pencil	56	2.99	167.44
7	4/1/2020	East	Jones	Binder	60	4.99	299.4
8	4/18/2020	Central	Kivell	Pencil	75	1.99	149.25
9	5/5/2020	Central	Kivell	Pencil	90	4.99	449.1
10	5/22/2020	West	Jardine	Pencil	32	1.99	63.68
11	6/8/2020	East	Jones	Binder	60	8.99	539.4
12	6/25/2020	Central	Kivell	Pencil	90	4.99	449.1
13	7/12/2020	East	Jones	Binder	29	1.99	57.71
14	7/29/2020	East	Jones	Binder	81	19.99	1,619.19
15	8/15/2020	East	Jones	Pencil	35	4.99	174.65
16	9/1/2020	Central	Kivell	Desk	2	125	250
17	9/18/2020	East	Jones	Pen Set	16	15.99	255.84
18	10/5/2020	Central	Kivell	Binder	28	8.99	251.72
19	10/22/2020	East	Jones	Pen	64	8.99	575.36
20	11/8/2020	East	Jones	Pen	15	19.99	299.85
21	11/25/2020	Central	Kivell	Pen Set	96	4.99	479.04
22	12/12/2020	Central	Kivell	Pencil	67	1.29	86.43
23	12/29/2020	East	Jones	Pen Set	74	15.99	1,183.26
24	1/15/2021	Central	Kivell	Binder	46	8.99	413.54
25	2/1/2021	Central	Kivell	Binder	87	15	1,305.00
26	2/18/2021	East	Jones	Binder	4	4.99	19.96
27	3/7/2021	West	Jardine	Binder	7	19.99	139.93
28	3/24/2021	Central	Kivell	Pen Set	50	4.99	249.5
29	4/10/2021	Central	Kivell	Pencil	66	1.99	131.34
30	4/27/2021	East	Jones	Pen	96	4.99	479.04
31	5/14/2021	Central	Kivell	Pencil	53	1.29	68.37
32	5/31/2021	Central	Kivell	Binder	80	8.99	719.2
33	6/17/2021	Central	Kivell	Desk	5	125	625
34	7/4/2021	East	Jones	Pen Set	62	4.99	309.38
35	7/21/2021	Central	Kivell	Pen Set	55	12.49	686.95
36	8/7/2021	Central	Kivell	Pen Set	42	23.95	1,005.90
37	8/24/2021	West	Jardine	Desk	3	275	825
38	9/10/2021	Central	Kivell	Pencil	7	1.29	9.03
39	9/27/2021	West	Jardine	Pen	76	1.99	151.24
40	10/14/2021	West	Jardine	Binder	57	19.99	1,139.43
41	10/31/2021	Central	Kivell	Pencil	14	1.29	18.06
42	11/17/2021	Central	Kivell	Binder	11	4.99	54.89
43	12/4/2021	Central	Kivell	Binder	94	19.99	1,879.06
44	12/21/2021	Central	Kivell	Binder	28	4.99	139.72
Sheet1

Cell Formulas
Range	Formula
C21:C44	C21	=INDEX(Sheet2!$B$1:$B$4,MATCH([@Region],Sheet2!$A$1:$A$4,FALSE))

 

[rlv01]

Maybe something like this.

Sub DoSomething()
    Dim WS As Worksheet
    Dim rng As Range
   
    Set WS = ActiveSheet
    With WS
        Set rng = .Range("A2:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
    Set rng = rng.Resize(rng.Rows.Count - 1)
    rng.Value = rng.Value
End Sub

 

[willow1985]

Maybe something like this.

Sub DoSomething()
    Dim WS As Worksheet
    Dim rng As Range
  
    Set WS = ActiveSheet
    With WS
        Set rng = .Range("A2:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
    Set rng = rng.Resize(rng.Rows.Count - 1)
    rng.Value = rng.Value
End Sub

This works. I was worried it would take a long time doing the entire table as it is over 40,000 rows but it worked pretty quick.

Thank you!

 

[rlv01]