Dynamically find the end of range - by Resize

[RAJESH1960]

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

 

[RAJESH1960]

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..

 

[alansidman]

We have a communication problem here. You are telling me what your code is doing. I am not interested in that. What I want to understand is what your objective is. You have data in one format that you wish to have in another format. In plain English describe what you wish to happen. Not what you are doing!! Once you explain your objective, I will attempt to create a workable VBA solution for you.

 

[RAJESH1960]

We have a communication problem here. You are telling me what your code is doing. I am not interested in that. What I want to understand is what your objective is. You have data in one format that you wish to have in another format. In plain English describe what you wish to happen. Not what you are doing!! Once you explain your objective, I will attempt to create a workable VBA solution for you.

?‍ I am sorry. I can’t explain you better than this. I have explained you everything in detail as simple as possible. If at all you understand any of the above I will be glad to receive an answer. I will try and send a link once again

 

[RAJESH1960]

?‍ I am sorry. I can’t explain you better than this. I have explained you everything in detail as simple as possible. If at all you understand any of the above I will be glad to receive an answer. I will try and send a link once again

Open the Macro and run step by step to understand better what is happening.
Anyone on the internet with this link can view.

Book1.xlsm

drive.google.com

 

[RAJESH1960]

Open the Macro and run step by step to understand better what is happening.
Anyone on the internet with this link can view.

Book1.xlsm

drive.google.com

My goal is to select the ledger names from Particulars and paste them in their respective columns "Bank / Cash" and "LedgerName" which are empty.

 

[alansidman]

Good Luck with solving your issue. I can not offer any further assistance based upon the information you have provided. Hopefully, there is some one here that will understand better your needs and explanation.

 

[RAJESH1960]

Good Luck with solving your issue. I can not offer any further assistance based upon the information you have provided. Hopefully, there is some one here that will understand better your needs and explanation.

Hopefully Yes? Thanks for your efforts

 

[RAJESH1960]

Good Luck with solving your issue. I can not offer any further assistance based upon the information you have provided. Hopefully, there is some one here that will understand better your needs and explanation.

Sir Finally I have successfully achieved what I needed. This is the only question which too long to find an answer in this board. But with the help of some videos online about relative references I was able to complete it. Here is the solution for that. Maybe this will help you to understand what I really wanted.
Option Explicit



Sub eureka()

'

' eureka Macro

'



'

Range("E2:F2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Clear

Range("F2").Select

ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Add2 Key:=Range( _

"G2:G236"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Add2 Key:=Range( _

"J2:J236"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets("Canara Bank").Sort

.SetRange Range("A1:K236")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Selection.AutoFilter

ActiveSheet.Range("$A$1:$K$236").AutoFilter Field:=7, Criteria1:="Contra"

ActiveSheet.Range("$A$1:$K$236").AutoFilter Field:=10, Criteria1:="<>"

Range("D2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

ActiveCell.Offset(0, 1).Range("A1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

Selection.AutoFilter

ActiveWindow.SmallScroll Down:=87

Selection.End(xlDown).Select

ActiveCell.Offset(1, -1).Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

ActiveCell.Offset(0, 2).Range("A1").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"

ActiveCell.Offset(0, -1).Range("A1").Select

ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Canara Bank").Sort.SortFields.Add2 Key:=ActiveCell _

.Offset(0, -4).Range("A1:A235"), SortOn:=xlSortOnValues, Order:=xlAscending _

, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Canara Bank").Sort

.SetRange ActiveCell.Offset(-1, -4).Range("A1:K236")

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

 

[RAJESH1960]

Got all messed up writing my code. The code doesn;t work in different sheets as expected

 

[MARK858]

Got all messed up writing my code. The code doesn;t work in different sheets as expected

@RAJESH1960 If this is a question then please elaborate on what your issue is and what you require.