VBA Question on Last Row

MBloem

New Member
Joined
May 15, 2009
Messages
6
I am trying to get the VBA to copy data from one sheet and append to another. Because I have formulas that return " " if no value is present it still grabs those cells too. How to I get it to take only the rows with value only to the other sheet? Any ideas?:eek:
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

MBloem

New Member
Joined
May 15, 2009
Messages
6
Sheets("UPLOAD SHEET (HIDE)").Select
Range(Range("A6"), Range("A6").End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Account Executive Upload Form.xls").Activate
Sheets("New Items").Select
Worksheets("New Items").Range("b65530").End(xlUp).Select
Excel.ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,020
Range("A65536").End(xlup) will return the last filled cell in column A, even if it contains a formula that evaluates to ""

Range("A:A").Find(What:="?*",After:=Range("A1"),SearchDirection:=xlPrevious) will return the last cell in column A that has a non-"" value, even if there are formulas below that cell.

The syntax of the .Find might be a bit off, but the key things are looking for the pattern matching string "?*" After:=Range("A1") and searching xlPrevious (rather than xlNext).
 

Watch MrExcel Video

Forum statistics

Threads
1,133,157
Messages
5,657,159
Members
418,363
Latest member
Debating_Earth

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
Top