Verifying Lookup Last Row..

[schafers]

Hi Kristie, Wanting to make sure I'm setting mine up right based off yours, if i use the lastrow function you have written, how would I tell it to copy from rows last row up through row a?

I've got data from 5 sheets, or tabs, and I want to tell the computer to compile them all into one list.

So something like

'finds last used row in column A
LastRow = Range("A65536").End(xlUp).Row

then

copy ("a:lastrow") to the new sheet? Want to make sure i'm on the right track.

thanks!!

 

[Von Pookie]

if i use the lastrow function you have written, how would I tell it to copy from rows last row up through row a?

Do you mean you want to copy from Row 1 of the column to the last row?

You can refer to it a couple of ways:

Range("A1",Cells(LastRow,"A"))

Range("A1:A" & LastRow)

Range("A1",Range("A" & LastRow))

All of these should refer to the same range, that being A1 to the row number being determined by the LastRow variable.

 

[schafers]

Exactly what I want to do. Then it would go to the next tab, which I know how to do, and find the last row on that page, then paste the data. So just so I don't get frustrated when attempting, the logic should be as follows.

copy from rows 1 - lastrow.

go to summary page.

find last row (should be blank) on summary page.

offset by 1 row to paste in blank row.

paste the data.

repeat onto next sheet.

I think I've got it now, but being still a newbie in VBA, slowly learning the right operator commands.

Thanks for your help.

 

[Von Pookie]

Just to make things a little simpler, I split this off into its own thread.

Finding the last row on the summary sheet is just the same
This is vague, but something of this sort should do it:

Set PasteRng = Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0)

So this looks at the summary sheet and finds the last used row in column A, then offsets that by one row down--which should be blank--and sets that as the range that the data will be pasted to.

 

[schafers]

So I crashed and burned. Here's what I have thus far, it keeps crashing out on me.

Sub ImportAdjustments()

Dim pasterng As Range
Dim CopyRng1 As Range
Dim CopyRng2 As Range
Dim CopyRng3 As Range
Dim CopyRng4 As Range
Dim CopyRng5 As Range

Dim LastRow As Long

'Find next blank row in column B
If Worksheets("Print Page.Send Page").Range("b1") = "" Then
LastRow = 1
Else
LastRow = Range("B65536").End(xlUp).Row + 1
End If

' Worksheets("print page.send page").Range("b1:e" & LastRow).Copy

Worksheets("print page.send page").Range("b:e").Copy

Set pasterng = Sheets("Summary").Range("B65536").End(xlUp).Offset(1, 0)
Worksheets("summary").Select
' worksheets("summary").Range(PasteRng).Select

Worksheets("Summary").Range(pasterng).PasteSpecial (xlValues)

End Sub

Trying to copy the rows from print page.send page to the last open row on summary page. I'll eventually be doing this with 5 pages of data, but once the first one is built, i should be able to copy the code for the other pages.

Thanks for all your help!!

 

[Von Pookie]

You shouldn't need to copy the code for each page--you can put it all in a nice loop to go through all of the pages in the workbook

But as to what went wrong with the code you have posted above: the only thing that's leaping out at me at the moment is that you have set PasteRng as a range variable, but then you used Range(PasteRng). That won't work for range variables. You can simply use PasteRng to refer to that range.

So it would be PasteRng.PasteSpecial xlPasteValues.

But when I run that I get an error on that line because your code is currently copying the entire columns B:E, and if column B on Summary is entirely blank, PasteRng will be set as B2--so you don't have enough room to paste all of the cells you just copied (if it was B1, there wouldn't be a problem.

You weren't too far off, though

Here's one way that works for me:

Sub ImportAdjustments()
Dim PasteRng As Range, LastRow As Long

'code inside this with statement will affect the _
Summary sheet (unless specified otherwise inside the statement)
With Sheets("Summary")
   'if B1 is blank, use B1 as range to paste to
   If .Range("B1") = "" Then
      Set PasteRng = .Range("B1")
   Else 'otherwise
      'find last used row in column B, set paste range offset down 1 row
      Set PasteRng = .Range("B65536").End(xlUp).Offset(1, 0)
   End If
End With

'code inside this with statement will affect the _
Print/Send page only
With Sheets("Print Page.Send Page")
   'find last used row in column B
   LastRow = .Range("B65536").End(xlUp).Row
   'copy from B1 to column E last row
   .Range("B1:E" & LastRow).Copy
End With

'Paste copied values to the sheet/cell assigned _
to the PasteRng variable
PasteRng.PasteSpecial xlValues

End Sub

 

[schafers]

Kristie, you are amazing!! It works like a champ. I added the same lines, and copied the next page of data to it, and it works like a champ!! WOOHOOO!!! I'm so excited!! Thanks so much!!

Now new problem, when i copy over the column from the original sheet, it's copying blank lines over and excel is reading them as a value, even though there is no data.

Any clue on how to tell Excel to look through the data and find a blank value, that's truly a blank value, versus a value that was posted? Thanks.
Steven

 

[Von Pookie]

Off of the top of my head, I don't know. I guess one possibility could be using the autofilter to hide rows that are blank in column B(?) and then just copy over the visible rows.

I'm not sure if that would work for you, though. I don't know how your data is set up or anything about it

 

[Tazguy37]

Just as an aside:

If you're ever planning on upgrading to Excel 12 when it comes out (and you really will use more than 65,536 rows), you may want to make code that finds the last row a little more dynamic. Try something like:

LastRow = .Range("B" & Rows.Count).End(xlUp).Row

Just my $0.02

 

[Von Pookie]

Ah, that's true.

That's not until next year, at least