Dynamically find the end of range - by Resize

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
This data is sorted by Voucher Type and Credit. I want to count the number of rows with value in column Credit and select the same number of rows in column Particulars in the same sheet and copy. I am able to do that perfectly with the code written in the sheet but if the count of number of rows changes in a different sheet, it selects the same number of rows. I have no knowledge of how to resize the same number of rows in the 2 different columns. This is the code that works in this sheet only
Option Explicit

Sub test()
'
' test Macro
'

'
Range("E2:F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Clear
Range("B2").Select
ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Add2 Key:=Range( _
"G2:G48"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Add2 Key:=Range( _
"J2:J48"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Canara Bank").Sort
.SetRange Range("A1:K48")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("J2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("D2:J21").Select
Range("J2").Activate
Selection.Copy
Range("M2").Select
ActiveSheet.Paste
Range("M2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("E2").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("F22").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D2:F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.FormulaR1C1 = "=R1C11"
Range("M2:M3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("M2:T21").Select
Selection.Clear
Range("A2").Select
ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Add2 Key:=Range( _
"A2:A48"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Canara Bank").Sort
.SetRange Range("A1:K48")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("E:F").Select
Columns("E:F").EntireColumn.AutoFit
Range("B2").Select
End Sub

Untitled.png
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
It will be much easier for us to work with an extract of your file instead of a picture. I don't want to have to re-enter all your data to determine a solution. Please upload your sample data using XL2BB. Additionally, it appears that you recorded your macro and it has a lot of extraneous lines. (not your fault--it happens when you record a macro). Please explain in simple English terms what you are wanting to achieve and we can accomodate.
 
Upvote 0
alansidman said:
It will be much easier for us to work with an extract of your file instead of a picture. I don't want to have to re-enter all your data to determine a solution. Please upload your sample data using XL2BB. Additionally, it appears that you recorded your macro and it has a lot of extraneous lines. (not your fault--it happens when you record a macro). Please explain in simple English terms what you are wanting to achieve and we can accomodate.
Click to expand...
I have tried to upload data before also but I couldn't, since I don't know how to.. Only if, for once, I knew how to upload data using XL2BB. Can you guide me with this.? I have tried to send you the link if it helps....
drive.google.com

Book1.xlsm

drive.google.com drive.google.com
 
Upvote 0
Did you look at the link in my signature block. It leads you to a clear explanation of how to use XL2BB. Also, I do not see an explanation in the thread of what you are hoping to achieve as asked in my previous post.

When I try to open your Google Drive File, I get an Access Denied. Further reason to use XL2BB.
 
Upvote 0
alansidman said:
Did you look at the link in my signature block. It leads you to a clear explanation of how to use XL2BB. Also, I do not see an explanation in the thread of what you are hoping to achieve as asked in my previous post.

When I try to open your Google Drive File, I get an Access Denied. Further reason to use XL2BB.
Book1.xlsm
ABCDEFGHIJKLM
1LineDateHelpParticularsBank / CashLedger NameVoucher TypeVoucher No.DebitCreditCanara Bank
2101-07-2020CashContra978100.00
3202-07-2020Kotak Mahindra BankContra979250.00
4303-07-2020Kotak Mahindra BankContra988300.00
5404-07-2020Bank of BarodaContra9764000.00
6505-07-2020ICICIContra9699000.00
7606-07-2020ICICIContra96310000.00
8707-07-2020Bank of BarodaContra96410000.00
9808-07-2020CashContra9665000.00
10909-07-2020JanuaryPayment1040236.00
111010-07-2020JanuaryPayment784236.00
121110-07-2020Bank of BarodaContra9764000.00
131211-07-2020JanuaryPayment785236.00
141311-07-2020ICICIContra9699000.00
151412-07-2020JulyPayment10501050.00
161512-07-2020ICICIContra96310000.00
171613-07-2020JanuaryPayment8631097.40
181713-07-2020Bank of BarodaContra96410000.00
191814-07-2020JanuaryPayment10331180.00
201914-07-2020CashContra9665000.00
212015-07-2020JulyPayment9203133.00
222115-07-2020CashContra978100.00
232216-07-2020JuneReceipt16795788.20Please run the code to understand what exactly it does.
242316-07-2020Kotak Mahindra BankContra979250.00The code runs perfectly in this data but with a different data and with a different range, the range copied is the same which is the problem.
252417-07-2020JuneReceipt16802457.69
262517-07-2020Kotak Mahindra BankContra988300.00
272618-07-2020MayReceipt16816795.00
282718-07-2020Bank of BarodaContra9764000.00
292819-07-2020ThursdayReceipt17177820.00
302919-07-2020ICICIContra9699000.00
313020-07-2020JuneReceipt1747822.41
323120-07-2020ICICIContra96310000.00
333221-07-2020CashContra978100.00
343321-07-2020Bank of BarodaContra96410000.00
353422-07-2020Kotak Mahindra BankContra979250.00
363522-07-2020CashContra9665000.00
373623-07-2020Kotak Mahindra BankContra988300.00
383723-07-2020
393824-07-2020Bank of BarodaBank of BarodaCanara BankContra9764000.00
403924-07-2020
414025-07-2020ICICIICICICanara BankContra9699000.00
424125-07-2020
434226-07-2020ICICICanara BankICICIContra96310000.00
444327-07-2020Bank of BarodaCanara BankBank of BarodaContra96410000.00
454428-07-2020CashCanara BankCashContra9665000.00
464529-07-2020CashCashCanara BankContra978100.00
474630-07-2020Kotak Mahindra BankKotak Mahindra BankCanara BankContra979250.00
484731-07-2020Kotak Mahindra BankKotak Mahindra BankCanara BankContra988300.00
49
Canara Bank
Cell Formulas
RangeFormula
F39,F46:F48,E43:E45,F41F39=$K$1
Click to expand...
 
Upvote 0
RAJESH1960 said:
This is the right one
Click to expand...
Book1.xlsm
ABCDEFGHIJKLM
1LineDateHelpParticularsBank / CashLedger NameVoucher TypeVoucher No.DebitCreditCanara Bank
2101-07-2020CashContra978100.00
3202-07-2020Kotak Mahindra BankContra979250.00
4303-07-2020Kotak Mahindra BankContra988300.00
5404-07-2020Bank of BarodaContra9764000.00
6505-07-2020ICICIContra9699000.00
7606-07-2020ICICIContra96310000.00
8707-07-2020Bank of BarodaContra96410000.00
9808-07-2020CashContra9665000.00
10909-07-2020JanuaryPayment1040236.00
111010-07-2020JanuaryPayment784236.00
121110-07-2020Bank of BarodaContra9764000.00
131211-07-2020JanuaryPayment785236.00
141311-07-2020ICICIContra9699000.00
151412-07-2020JulyPayment10501050.00
161512-07-2020ICICIContra96310000.00
171613-07-2020JanuaryPayment8631097.40
181713-07-2020Bank of BarodaContra96410000.00
191814-07-2020JanuaryPayment10331180.00
201914-07-2020CashContra9665000.00
212015-07-2020JulyPayment9203133.00
222115-07-2020CashContra978100.00
232216-07-2020JuneReceipt16795788.20Please run the code to understand what exactly it does.
242316-07-2020Kotak Mahindra BankContra979250.00The code runs perfectly in this data but with a different data and with a different range, the range copied is the same which is the problem.
252417-07-2020JuneReceipt16802457.69
262517-07-2020Kotak Mahindra BankContra988300.00
272618-07-2020MayReceipt16816795.00
282718-07-2020Bank of BarodaContra9764000.00
292819-07-2020ThursdayReceipt17177820.00
302919-07-2020ICICIContra9699000.00
313020-07-2020JuneReceipt1747822.41
323120-07-2020ICICIContra96310000.00
333221-07-2020CashContra978100.00
343321-07-2020Bank of BarodaContra96410000.00
353422-07-2020Kotak Mahindra BankContra979250.00
363522-07-2020CashContra9665000.00
373623-07-2020Kotak Mahindra BankContra988300.00
383723-07-2020June
393824-07-2020Bank of BarodaContra9764000.00
403924-07-2020MayReceipt16816795.00
414025-07-2020ICICIContra9699000.00
424125-07-2020ThursdayReceipt17177820.00
434226-07-2020ICICIContra96310000.00
444327-07-2020Bank of BarodaContra96410000.00
454428-07-2020CashContra9665000.00
464529-07-2020CashContra978100.00
474630-07-2020Kotak Mahindra BankContra979250.00
484731-07-2020Kotak Mahindra BankContra988300.00
Canara Bank
 
Upvote 0
I have run your code as you asked. The results of that action is below. I don't believe this is what you wish to happen. I will ask one last time. Please explain step by step explicitly what you wish to happen using the data in your upload. Because what you have presented for code does not make any sense when run.

testt.xlsm
BCDEFGHIJK
21/7/2020CashCash0Contra978100
32/7/2020Kotak Mahindra BankKotak Mahindra Bank0Contra979250
43/7/2020Kotak Mahindra BankKotak Mahindra Bank0Contra988300
54/7/2020Bank of BarodaBank of Baroda0Contra9764000
65/7/2020ICICIICICI0Contra9699000
76/7/2020ICICIICICI0Contra96310000
87/7/2020Bank of Baroda0Bank of BarodaContra96410000
98/7/2020Cash0CashContra9665000
109/7/2020January0JanuaryPayment1040236
1110/7/2020January0JanuaryPayment784236
1210/7/2020Bank of BarodaBank of Baroda0Contra9764000
1311/7/2020January0JanuaryPayment785236
1411/7/2020ICICIICICI0Contra9699000
1512/7/2020July0JulyPayment10501050
1612/7/2020ICICI0ICICIContra96310000
1713-07-2020January0JanuaryPayment8631097.4
1813-07-2020Bank of Baroda0Bank of BarodaContra96410000
1914-07-2020January0JanuaryPayment10331180
2014-07-2020Cash0CashContra9665000
2115-07-2020July0JulyPayment9203133
2215-07-2020CashCash0Contra978100
2316-07-2020June0JuneReceipt16795788.2
2416-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra979250
2517-07-2020June0JuneReceipt16802457.69
2617-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra988300
2718-07-2020May0MayReceipt16816795
2818-07-2020Bank of BarodaBank of Baroda0Contra9764000
2919-07-2020Thursday0ThursdayReceipt17177820
3019-07-2020ICICIICICI0Contra9699000
3120-07-2020June0JuneReceipt1747822.41
3220-07-2020ICICI0ICICIContra96310000
3321-07-2020CashCash0Contra978100
3421-07-2020Bank of Baroda0Bank of BarodaContra96410000
3522-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra979250
3622-07-2020Cash0CashContra9665000
3723-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra988300
3823-07-2020June0June
3924-07-2020Bank of BarodaBank of Baroda0Contra9764000
4024-07-2020May0MayReceipt16816795
4125-07-2020ICICIICICI0Contra9699000
4225-07-2020Thursday0ThursdayReceipt17177820
4326-07-2020ICICI0ICICIContra96310000
4427-07-2020Bank of Baroda0Bank of BarodaContra96410000
4528-07-2020Cash0CashContra9665000
4629-07-2020CashCash0Contra978100
4730-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra979250
48DateHelpParticulars0ParticularsVoucher TypeVoucher No.DebitCreditCanara Bank
4931-07-2020Kotak Mahindra Bank0Kotak Mahindra BankContra988300
Canara Bank
Cell Formulas
RangeFormula
E48:E49,F46:F47,E42:E45,F41,E40,F39,E38,F37,E36,F35,E34,F33,E31:E32,F30,E29,F28,E27,F26,E25,F24,E23,F22,E15:E21,F14,E13,F12,E8:E11,F2:F7F2=$K$1
 
Upvote 0
alansidman said:
I have run your code as you asked. The results of that action is below. I don't believe this is what you wish to happen. I will ask one last time. Please explain step by step explicitly what you wish to happen using the data in your upload. Because what you have presented for code does not make any sense when run.

testt.xlsm
BCDEFGHIJK
21/7/2020CashCash0Contra978100
32/7/2020Kotak Mahindra BankKotak Mahindra Bank0Contra979250
43/7/2020Kotak Mahindra BankKotak Mahindra Bank0Contra988300
54/7/2020Bank of BarodaBank of Baroda0Contra9764000
65/7/2020ICICIICICI0Contra9699000
76/7/2020ICICIICICI0Contra96310000
87/7/2020Bank of Baroda0Bank of BarodaContra96410000
98/7/2020Cash0CashContra9665000
109/7/2020January0JanuaryPayment1040236
1110/7/2020January0JanuaryPayment784236
1210/7/2020Bank of BarodaBank of Baroda0Contra9764000
1311/7/2020January0JanuaryPayment785236
1411/7/2020ICICIICICI0Contra9699000
1512/7/2020July0JulyPayment10501050
1612/7/2020ICICI0ICICIContra96310000
1713-07-2020January0JanuaryPayment8631097.4
1813-07-2020Bank of Baroda0Bank of BarodaContra96410000
1914-07-2020January0JanuaryPayment10331180
2014-07-2020Cash0CashContra9665000
2115-07-2020July0JulyPayment9203133
2215-07-2020CashCash0Contra978100
2316-07-2020June0JuneReceipt16795788.2
2416-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra979250
2517-07-2020June0JuneReceipt16802457.69
2617-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra988300
2718-07-2020May0MayReceipt16816795
2818-07-2020Bank of BarodaBank of Baroda0Contra9764000
2919-07-2020Thursday0ThursdayReceipt17177820
3019-07-2020ICICIICICI0Contra9699000
3120-07-2020June0JuneReceipt1747822.41
3220-07-2020ICICI0ICICIContra96310000
3321-07-2020CashCash0Contra978100
3421-07-2020Bank of Baroda0Bank of BarodaContra96410000
3522-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra979250
3622-07-2020Cash0CashContra9665000
3723-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra988300
3823-07-2020June0June
3924-07-2020Bank of BarodaBank of Baroda0Contra9764000
4024-07-2020May0MayReceipt16816795
4125-07-2020ICICIICICI0Contra9699000
4225-07-2020Thursday0ThursdayReceipt17177820
4326-07-2020ICICI0ICICIContra96310000
4427-07-2020Bank of Baroda0Bank of BarodaContra96410000
4528-07-2020Cash0CashContra9665000
4629-07-2020CashCash0Contra978100
4730-07-2020Kotak Mahindra BankKotak Mahindra Bank0Contra979250
48DateHelpParticulars0ParticularsVoucher TypeVoucher No.DebitCreditCanara Bank
4931-07-2020Kotak Mahindra Bank0Kotak Mahindra BankContra988300
Canara Bank
Cell Formulas
RangeFormula
E48:E49,F46:F47,E42:E45,F41,E40,F39,E38,F37,E36,F35,E34,F33,E31:E32,F30,E29,F28,E27,F26,E25,F24,E23,F22,E15:E21,F14,E13,F12,E8:E11,F2:F7F2=$K$1
Click to expand...
Yes that is correct. Just that, in the place of 0 I need the name of the bank in cell K1
 
Upvote 0
alansidman, I have this project running successfully where I import data from excel to tally. To make things a bit easier I am trying to create this code. This is the bank statement sheet which I have to edit and make it ready to import. Inserting a few columns for workings, the Columns “Bank/Cash” and “Ledger Name” are 2 of those columns inserted in this sheet.

  • As there may be formats copied while inserting the columns, with the help of a macro I clear that data.
  • I sort the data with “voucher type” and with Add Level “credit”.
  • I select the data in the “Credit” column with the help of Range(Selection, Selection.End(xlDown)).Select which will select the data from J2 to the next empty cell. (The range will change depending on the data in each bank.)
  • I have mentioned the name of the bank in K1 (Name of the bank whose statement I am importing). The name of the bank will change during different imports.
  • Now one column either “Bank/Cash” and “Ledger Name” has to contain the bank name.
  • The range selected in column “Credit” should resize the number of selected rows with the column “Particulars” and select them and copy.
  • Here as I didn’t know how to resize or count the number of rows, I selected the range from J:D and copied in column N
  • The data was posted in column N to T. I selected the range from N2 to the last empty cell with the help of Range(Selection, Selection.End(xlDown)).and copied it
  • Then I selected Cell E2 and pasted it.
  • The copied data must be pasted in the corresponding “Ledger Name” column.
  • After pasting I used relative references and selected the next empty cell in “Ledger Name” column from where I went to 2 cells to the left, i.e., “particulars” column.
  • From that cell I selected the whole range below with the help of Range(Selection, Selection.End(xlDown)) and copied it
  • I selected the next empty cell from “Bank / Cash”” column and pasted it.
  • I selected the range D2:F2 and again with the help of Range(Selection, Selection.End(xlDown))
  • I used “go” function (Ctrl+G) and in it I selected blanks. In the visible empty cell I typed K1 and used F4 to freeze it, i.e., $F$4 and pressed control enter
  • I cleared the data in columns N: J
  • I sort the data again sort by the column “Line”
  • My sheet was again ready to import
  • As this is the same routine with every bank, I wanted to create this Macro and make the work simpler.
Now the problem, when I run the above code in a different bank statement with the same columns but with different data the selection in column “Credit is the same range” which is incorrect selection. This is where I need help..
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dazzawm
  • Solved
Slight Amendment To Code
Replies
6
Views
292
Dazzawm
Dazzawm
C
  • Question
Daily Run data (copy data in loop by daily in worksheet)
Replies
3
Views
161
dermie_72
D
SandsB
  • Question
Sorting when a column or 2 is empty
Replies
1
Views
158
DanteAmor
DanteAmor
F
  • Solved
VBA for sorting data with range length that isn't fixed
Replies
2
Views
306
feni1388
F
A
  • Solved
VBA - button to sort by date in ascending and descending order
Replies
6
Views
375
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,012
Messages
6,122,682
Members
449,091
Latest member
peppernaut

Share this page

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