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]

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

Mark. It has been a few days I posted this post. Since then I have been trying to get an answer. Can you view the question or do you want me to post it again. ?

 

[MARK858]

No I want you to explain your issues with your last code posted (and you posted the statement 33 minutes ago not a few days, it needs elaborating on).

 

[RAJESH1960]

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

In the above code, if Column D has data in 2 or more cells then it works perfect. But if there is one entry only and no entry at all it goes haywire.
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
this is the part which needs amendment

 

[RAJESH1960]

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

In the above code, if Column D has data in 2 or more cells then it works perfect. But if there is one entry only and no entry at all it goes haywire.
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
this is the part which needs amendment

 

[RAJESH1960]

If you have just one amount in the credit column, the code should run till the end. I it doesn"t have any amount in the credit column then also the code should run.

 

[MARK858]

If you have just one amount in the credit column, the code should run till the end. I it doesn"t have any amount in the credit column then also the code should run.

The code runs when you tell it to so what is it not doing that you want it to do?
Explain what you mean by

the code should run till the end

Is it erring? is it stopping at a certain point? You really do need to give more detail and explanation in your questions as we can't see what you can see happening so you need to give us detail to be able to give suggestions.

 

[RAJESH1960]

The code runs when you tell it to so what is it not doing that you want it to do?
Explain what you mean by

Is it erring? is it stopping at a certain point? You really do need to give more detail and explanation in your questions as we can't see what you can see happening so you need to give us detail to be able to give suggestions.

If the number of entries in credit is 1, after sorting and using filter functions, then the code selects column D2 to the last line in excel. It has to select only one entry. I want the code to count the cells from D2 to the next empty cell only. If there are no entries at all then it should remove the filters and select the data from column D2 to the next empty cell , copy and paste to column F2.

 

[RAJESH1960]

After sorting and filter functions, In this sheet as there are more than 2 entries in the credit column. Hence the full code runs perfect.

 

[RAJESH1960]

But in this case, after sort and filter functions, there is only one entry hence this function gives a completely incorrect answer.

 

[RAJESH1960]

But in this case, after sort and filter functions, there is only one entry hence this function gives a completely incorrect answer.

Each statement will have different number of entries. So if I can get a code, where in any statement, despite the number of entries after sort and filter, the credit column which have data in the cell, may it be none or more than 2 the code should do its work.
Is there a way I could send you the video recording or share my sheet.??