Resize and select rows and columns

[RAJESH1960]

Hello once again

The data in the columns A to H are in vertical order. I have converted the data into a horizontal order as required with the help of formulas. With the help of a code, the data was pasted in cells A3:H24 which may vary in different circumstances. What I am trying to do is select data from L3:BE7 in this case with the help of a code. The code should select cell L3, count the number of rows in column K with value and select as many rows and columns from L to BE. I have to copy that range and paste it to sheet E with paste special to remove the formulas. I also had to clear the formats from the blank cells in sheet E. for which I have added another line.
I was using this to complete it. Please note that in this sample sheet only 4 rows are to be selected. The selection may range from 100 - 500 rows in different bank sheets.
Range("L3:BE500").Select
Selection.Copy
Sheets("E").Select
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With ActiveSheet.UsedRange
.Value = .Value

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE
1
2	Line	Date	Vch Type	Vch No.	Narration	Particulars	Debit Negative	Credit Positive		Total Amt		Date	Vch Type	Vch No.	Narration	Ledger 1	Amt	Ledger 2	Amt	Ledger 3	Amt	Ledger 4	Amt	Ledger 5	Amt	Ledger 6	Amt	Ledger 7	Amt	Ledger 8	Amt	Ledger 9	Amt	Ledger 10	Amt	Ledger 11	Amt	Ledger 12	Amt	Ledger 13	Amt	Ledger 14	Amt	Ledger 15	Amt	Ledger 16	Amt	Ledger 17	Amt	Ledger 18	Amt	Ledger 19	Amt	Ledger 20	Amt	Ledger 21	Amt
3	1	30-06-2021	Receipt	100		Bank	-4040.00			-4040.00	1	30-06-2021	Receipt	100		Bank	-4040	Sunday	1015	Monday	2025	Tuesday	1000.5	Round Off	-0.5
4	2	30-06-2021	Receipt	100		Sunday		1015.00		1015.00	2	04-07-2021	Payment	103		Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
5	3	30-06-2021	Receipt	100		Monday		2025.00		2025.00	3	05-07-2021	Receipt	104		Bank	-4040	Sunday	1015	Monday	2025	Tuesday	1000.5	Round Off	-0.5
6	4	30-06-2021	Receipt	100		Tuesday		1000.50		1000.50	4	09-07-2021	Payment	107		Bank	1001	January	-100	February	-200	March	-300	April	-400	Round Off	-1
7	5	30-06-2021	Receipt	100		Round Off	-0.50			-0.50
8	8	04-07-2021	Payment	103		Bank		1001.00		1001.00
9	9	04-07-2021	Payment	103		January	-100.00			-100.00
10	10	04-07-2021	Payment	103		February	-200.00			-200.00
11	11	04-07-2021	Payment	103		March	-300.00			-300.00
12	12	04-07-2021	Payment	103		April	-400.00			-400.00
13	13	04-07-2021	Payment	103		Round Off	-1.00			-1.00
14	14	05-07-2021	Receipt	104		Bank	-4040.00			-4040.00
15	15	05-07-2021	Receipt	104		Sunday		1015.00		1015.00
16	16	05-07-2021	Receipt	104		Monday		2025.00		2025.00
17	17	05-07-2021	Receipt	104		Tuesday		1000.50		1000.50
18	18	05-07-2021	Receipt	104		Round Off	-0.50			-0.50
19	21	09-07-2021	Payment	107		Bank		1001.00		1001.00
20	22	09-07-2021	Payment	107		January	-100.00			-100.00
21	23	09-07-2021	Payment	107		February	-200.00			-200.00
22	24	09-07-2021	Payment	107		March	-300.00			-300.00
23	25	09-07-2021	Payment	107		April	-400.00			-400.00
24	26	09-07-2021	Payment	107		Round Off	-1.00			-1.00
25										0.00
26
B

Cell Formulas
Range	Formula
K3	K3	=IF(L3="","",1)
K4:K19	K4	=IF(L4="","",K3+1)

 

[Fluff]

How about

Sub Rajesh()
   Dim Mx As Long
   Mx = Application.Max(Range("K3:K1000"))
   Sheets("E").Range("A3:BE3").Resize(Mx).Value = Range("L3:BE3").Resize(Mx).Value
End Sub

 

[RAJESH1960]

How about

Sub Rajesh()
   Dim Mx As Long
   Mx = Application.Max(Range("K3:K1000"))
   Sheets("E").Range("A3:BE3").Resize(Mx).Value = Range("L3:BE3").Resize(Mx).Value
End Sub

Hahahahahaha. I ran the code and was waiting for the code to select the range in B sheet. After nothing happened and after sometime I checked the E sheet and the code had already given the result.
Thanks Fluff.

 

[RAJESH1960]

How about

Sub Rajesh()
   Dim Mx As Long
   Mx = Application.Max(Range("K3:K1000"))
   Sheets("E").Range("A3:BE3").Resize(Mx).Value = Range("L3:BE3").Resize(Mx).Value
End Sub

One more code to alter and my project will be completed.

 

[Fluff]

You're welcome & thanks for the feedback.