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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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,270
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).
 

Forum statistics

Threads
1,176,341
Messages
5,902,589
Members
434,981
Latest member
sheils

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