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

 

[MARK858]

You don't need to share your sheet. We can copy from an XL2BB posting to a sheet easy enough. I am just trying to read through your various posts to try and get a clear understanding of your issue.

 

[MARK858]

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

Just concentrating on the above, what do you want to happen exactly if there is only an entry in D2? and if your answer is anything like "to the first blank cell" then do you actually mean the first blank cell or actually the first cell after the last used cell in column D?

 

[RAJESH1960]

Just concentrating on the above, what do you want to happen exactly if there is only an entry in D2? and if your answer is anything like "to the first blank cell" then do you actually mean the first blank cell or actually the first cell after the last used cell in column D?

If there is one entry in column D then It should copy the data from that cell and paste it to the same row in column E. The next code should remove the filter and using relative reference, from the active cell, it should go one row down and one column left and select the balance data from that cell to the next blank cell, copy and paste it in the same row that is Column F again using relative reference.

 

[RAJESH1960]

If there is one entry in column D the code stops here
ActiveCell.Offset(1, -1).Range("A1").Select

 

[RAJESH1960]

If there is one entry in column D the code stops here
ActiveCell.Offset(1, -1).Range("A1").Select

Time to hit the sack. Will check with you tomorrow. Good Night

 

[MARK858]

Okay, when you have only D2 filled End(xlDown) will be selecting from D2 to the last cell in the column and when you do the Offset(0, 1).Select you are selecting again until the last cell in the column then you are trying to Selection.End(xlDown).

This makes the ActiveCell $E$1048576 then you are trying to do ActiveCell.Offset(1, -1).

You can't offset the ActiveCell by one row because that takes it below the last row in the sheet and so it errors.

Maybe the below does what you want

    With Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
        .Copy .Cells(1).Offset(, 1)
        .Cells(1).Offset(, 1).AutoFilter
        .Cells(.Rows.Count).Offset(1).Select
    End With

 

[RAJESH1960]

Okay, when you have only D2 filled End(xlDown) will be selecting from D2 to the last cell in the column and when you do the Offset(0, 1).Select you are selecting again until the last cell in the column then you are trying to Selection.End(xlDown).

This makes the ActiveCell $E$1048576 then you are trying to do ActiveCell.Offset(1, -1).

You can't offset the ActiveCell by one row because that takes it below the last row in the sheet and so it errors.

Maybe the below does what you want

    With Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
        .Copy .Cells(1).Offset(, 1)
        .Cells(1).Offset(, 1).AutoFilter
        .Cells(.Rows.Count).Offset(1).Select
    End With

Now you are getting to understand. Now the code works if there is data in one cell or multiple cells in Column D. To perfect and complete this code. one,if function is needed before that code. If the With Range("D2:D" & Range("D" & Rows.Count) is empty, it selects the wrong range here as shown in the image.
If there are ain't any entries in Range D2:D, then after autofilter it should copy the whole Range D2:D and paste it in Column E2

 

[RAJESH1960]

Now you are getting to understand. Now the code works if there is data in one cell or multiple cells in Column D. To perfect and complete this code. one,if function is needed before that code. If the With Range("D2:D" & Range("D" & Rows.Count) is empty, it selects the wrong range here as shown in the image.
If there are ain't any entries in Range D2:D, then after autofilter it should copy the whole Range D2:D and paste it in Column E2

A.

 

[MARK858]

I'll look at it when I get home from work tonight. It only needs a simple If statement.

 

[RAJESH1960]

I'll look at it when I get home from work tonight. It only needs a simple If statement.

exactly.