-2

I'm trying to copy only data (excluding blanks created by If statement) from "Data" tab then paste to the bottom of a data column on the "Summary" tab. The trouble arises from trying to figure out how to get VBA to recognize the range of usable data.

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
Theo
  • 1
  • Do you have code that is giving you an error currently? See [how to ask a question](http://stackoverflow.com/help/on-topic) for more information. – Chrismas007 Aug 14 '15 at 18:35
  • lol you could have figured this out in 30 secs with a google search. To get to the bottom use `lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row` Be sure to change sheet and column as necessary. – findwindow Aug 14 '15 at 19:08
  • Yes, that would work if I didn't have blank created by an IF() statement. Therefore, the count method does not actually return the last row data. – Theo Aug 14 '15 at 21:00

1 Answers1

0

There are a couple of different ways to do this, depending on what you need. Here's one SO thread that discusses a few uses. Here's another page that discusses using UsedRange or .Rows. And, as @findwindow noted, you can use .xlEnd.

This is a pretty common use of VBA, so if you Google around (or even look through SO), you'll find some information.

Edit: Per your comment, just set a range, and loop through the cells in the range until you find a non-numeric number:

Dim rng as Range, cel as Range
Dim lastRow as Integer
Set rng = Range("A1:A10000")
for each cel in rng
 If not isnumeric(cel.value) then 
   'Do whatever code you want, when the cell is NOT numeric, ie
   lastRow = cel.Row
End if
next cel
Community
  • 1
  • 1
BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • I have already tried those methods, but my dynamic data range includes blanks from an IF() statement. The methods you mentions gives the last row with the formula not last row of actual data. I was looking for a way to include a looping function to test each cell for IsNumber() until it finds false then use that value as my range. – Theo Aug 14 '15 at 21:01