Using Range & Rowscount

DataBlake

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
Code: 
 Dim lastRow As Long
 destrow = Sheets("Master Wheel").Range("A" & Rows.Count).End(xlUp).Row
 
    Columns("T:T").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("T2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-19],'Master Wheel'!R1:R1048576,1,FALSE)),""err"",""delete"")"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("T2").AutoFill Destination:=Range("T2:T" & lastRow)
    
    Columns("T:T").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Cells.Select

    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Add Key:=Range( _
        "T2:T" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("WheelPros").Sort
        .SetRange Range("A1:XF1000000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Columns("T:T").Select
    Selection.Find(What:="err", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    ActiveCell.EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Master Wheel").Select
    Range("A" & destrow).Select
    ActiveSheet.Paste

Hey so i have this code that i'm trying to make work.
Everything is fine unless there is only 1 item in column T
for that reason i'm trying to figure out how i can get the range from the activecell "err" (row)
to the bottom of rows.count

Code: 
Range(selection, Rows.Count)).select
gives me an error

any help would be appreciated
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Also tried using
Code: 
Range(Cells(ActiveCell.Row, lastCol), Cells(lastRow, lastCol)).Select
and im still getting an error
 
Upvote 0
Also tried usingCode:
Range(Cells(ActiveCell.Row, lastCol), Cells(lastRow, lastCol)).Select
and im still getting an error
Click to expand...
I don't see a "lastCol" calculation in your code.
 
Upvote 0
Joe4 said:
I don't see a "lastCol" calculation in your code.
Click to expand...

well i added lastCol as long that used
Code: 
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
but that doesn't really matter.
What i'm trying to accomplish is the first code where it says
Code: 
Range(Selection, Selection.End(xlDown)).Select
i need it to select the current cell's row and every row below it

Edit:
the only thing i've fixed that i think is necessary is
Code: 
Selection.Copy
    Sheets("Master Wheel").Select
    Range("A" & destrow).Select
    ActiveCell.EntireRow.Select
    ActiveSheet.Paste

because before (in my first code) it was just posting column A when i needed the whole row to be pasted.
 
Last edited:
Upvote 0
If it does not find what you are looking for in your "FIND" statement, you will get an error.
 
Upvote 0
Joe4 said:
If it does not find what you are looking for in your "FIND" statement, you will get an error.
Click to expand...

that sounds good to me. I don't want it to do anything if it can't find the "err"
 
Upvote 0
See if those does what you want. If it does not find it, it will just pop-up a message box letting you know:
Code: 
Sub MyMacro()

    Dim destrow As Long
    Dim lastRow As Long
    
    destrow = Sheets("Master Wheel").Range("A" & Rows.Count).End(xlUp).Row
 
    Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("T2").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-19],'Master Wheel'!R1:R1048576,1,FALSE)),""err"",""delete"")"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("T2").AutoFill Destination:=Range("T2:T" & lastRow)
    
    Columns("T:T").Copy
    Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Cells.Select

    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Add Key:=Range( _
        "T2:T" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("WheelPros").Sort
        .SetRange Range("A1:XF1000000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    On Error GoTo err_chk
    Columns("T:T").Find(What:="err", After:=Range("T1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
        
    ActiveCell.EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Copy
    Sheets("Master Wheel").Select
    Range("A" & destrow).Select
    ActiveSheet.Paste
    
    Exit Sub
    
    
err_chk:
    MsgBox "Cannot find string value of 'err' in column T"
    
End Sub
 
Upvote 0
Joe4 said:
See if those does what you want. If it does not find it, it will just pop-up a message box letting you know:
Code: 
Sub MyMacro()

    Dim destrow As Long
    Dim lastRow As Long
    
    destrow = Sheets("Master Wheel").Range("A" & Rows.Count).End(xlUp).Row
 
    Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("T2").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-19],'Master Wheel'!R1:R1048576,1,FALSE)),""err"",""delete"")"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("T2").AutoFill Destination:=Range("T2:T" & lastRow)
    
    Columns("T:T").Copy
    Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Cells.Select

    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Add Key:=Range( _
        "T2:T" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("WheelPros").Sort
        .SetRange Range("A1:XF1000000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    On Error GoTo err_chk
    Columns("T:T").Find(What:="err", After:=Range("T1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
        
    ActiveCell.EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Copy
    Sheets("Master Wheel").Select
    Range("A" & destrow).Select
    ActiveSheet.Paste
    
    Exit Sub
    
    
err_chk:
    MsgBox "Cannot find string value of 'err' in column T"
    
End Sub
Click to expand...

the problem with this is the
Code: 
Range(Selection, Selection.End(xlDown)).Copy

because if there is just one singular "err" then the copy range and the paste range are not equal
if there are two or more "err" its fine
I need to replace the above code with selecting the active cell's row and whatever the lastRow is. If the active cell row IS the lastRow it just copies the one row over to the "Master Wheel" sheet
 
Upvote 0
What if we try these modifications?
Code: 
Sub TestRow()

    Dim destrow As Long
[COLOR=#ff0000]    Dim curRow As Long[/COLOR]
    Dim lastRow As Long
    
    destrow = Sheets("Master Wheel").Range("A" & Rows.Count).End(xlUp).Row
 
    Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("T2").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-19],'Master Wheel'!R1:R1048576,1,FALSE)),""err"",""delete"")"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("T2").AutoFill Destination:=Range("T2:T" & lastRow)
    
    Columns("T:T").Copy
    Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Cells.Select

    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Add Key:=Range( _
        "T2:T" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("WheelPros").Sort
        .SetRange Range("A1:XF1000000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    On Error GoTo err_chk
    Columns("T:T").Find(What:="err", After:=Range("T1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
    
[COLOR=#ff0000]    curRow = ActiveCell.Row[/COLOR]
[COLOR=#ff0000]    Range(curRow & ":" & lastRow).Copy[/COLOR]
    Sheets("Master Wheel").Select
    Range("A" & destrow).Select
    ActiveSheet.Paste
    
    Exit Sub
    
    
err_chk:
    MsgBox "Cannot find string value of 'err' in column T"
    
End Sub
 
Upvote 0
Joe4 said:
What if we try these modifications?
Code: 
Sub TestRow()

    Dim destrow As Long
[COLOR=#ff0000]    Dim curRow As Long[/COLOR]
    Dim lastRow As Long
    
    destrow = Sheets("Master Wheel").Range("A" & Rows.Count).End(xlUp).Row
 
    Columns("T:T").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("T2").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-19],'Master Wheel'!R1:R1048576,1,FALSE)),""err"",""delete"")"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("T2").AutoFill Destination:=Range("T2:T" & lastRow)
    
    Columns("T:T").Copy
    Columns("T:T").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Cells.Select

    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("WheelPros").Sort.SortFields.Add Key:=Range( _
        "T2:T" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("WheelPros").Sort
        .SetRange Range("A1:XF1000000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    On Error GoTo err_chk
    Columns("T:T").Find(What:="err", After:=Range("T1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
    
[COLOR=#ff0000]    curRow = ActiveCell.Row[/COLOR]
[COLOR=#ff0000]    Range(curRow & ":" & lastRow).Copy[/COLOR]
    Sheets("Master Wheel").Select
    Range("A" & destrow).Select
    ActiveSheet.Paste
    
    Exit Sub
    
    
err_chk:
    MsgBox "Cannot find string value of 'err' in column T"
    
End Sub
Click to expand...

thank you so much
i was unaware you could do the ":" bit when making a range
it works perfectly
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Help with combining 2 macros into 1
Replies
8
Views
263
MARK858
MARK858
M
  • Question Question
How To Loop Macro
Replies
6
Views
499
rlv01
R
M
  • Question Question
Macro - find (control+f) value copied earlier in process
Replies
9
Views
317
Alex Blakenburg
A
H
  • Solved
Sorting and Deleting VBA for large file
2
Replies
16
Views
776
heathball
H
E
  • Solved
ActiveCell vs Active range search
Replies
3
Views
440
Joe4
Joe4

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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