Last Row Question

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
943
Office Version
  1. 2016
Platform
  1. Windows
The code below is working for me to find and move the Order ID column. However, I'm using down to row 50,000. How do I incorporate Last Row instead of using just a generic row 50,000?
Column F will always have data all the way down.
Thanks for the help

VBA Code:
'Find and Place "Order Id" Column
Set fnd = Range("1:1").Find("Order_Id", , , xlWhole, , , False, , False)
If Not fnd Is Nothing Then
    Intersect(Range("1:50000"), fnd.EntireColumn).Copy
    Range("F1").Insert Shift:=xlToRight
    Intersect(Range("1:50000"), fnd.EntireColumn).ClearContents
End If
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
How about
VBA Code:
Set fnd = Range("1:1").Find("Order_Id", , , xlWhole, , , False, , False)
If Not fnd Is Nothing Then
    Intersect(activesheet.usedrange, fnd.EntireColumn).Copy
    Range("F1").Insert Shift:=xlToRight
    Intersect(activesheet.usedrange, fnd.EntireColumn).ClearContents
End If
 
Upvote 0
How about
VBA Code:
Set fnd = Range("1:1").Find("Order_Id", , , xlWhole, , , False, , False)
If Not fnd Is Nothing Then
    Intersect(activesheet.usedrange, fnd.EntireColumn).Copy
    Range("F1").Insert Shift:=xlToRight
    Intersect(activesheet.usedrange, fnd.EntireColumn).ClearContents
End If

I believe this is working, as it finds the header name, will it copy over the blank cells all the way to the bottom, in that column? it looks like it is doing this,
Thanks for the help
 
Upvote 0
It will only copy data for the used range.
 
Upvote 0
It will only copy data for the used range.

So If I have data all the way to F63, then there are blank cells, and the data starts again at F72, will it continue over the blank cells to F72?
Thanks
 
Upvote 0
It will copy all the data in that column.
 
Upvote 0
ok, i see what it is doing, it is copying all data, and skipping over blank cells and continuing. so it is working.
Thanks
However, it is still going to line 50,000 because of the previous code. do you want to look at that?
THanks for the help
 
Upvote 0
If it's going down too far then you have hidden formatting or something below the data.
Select all rows below your data, right click & select Delete, then save the workbook.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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