Solved: Quick syntax issue on find last row

[DanOfEarth]

Sorry...

I lost all of my code snippets, even on the simplist of things. :beg:

Obviously I'm trying to paste Activecell info into the last blank space in column (A). The below isn't working. This is part of a sub() on a Command Button. I'd rather not Select the "last row cell" before pasting because I'm trying not to lose my old selection for reference purposes. I have the feeling it should be a "value=" property something similar.


Selection.Cut
Range("A" & LastRow + 1).Paste


(Dang I love the Smilies list. I need to add them to my Battlefield 2 forums.)

[GTO]

Greetings Dan,

You have Selection in your example, rather than ActiveCell. This should grab the selection and place it below the last used cell...
Option Explicit
Sub whatever()
Dim lRow As Long

lRow = Cells(65536, 1).End(xlUp).Row + 1
Selection.Copy Range("A" & lRow)

End Sub
Hope this helps,

Mark

[DanOfEarth]

Thanks Mark,

I'm a little thick this morning. I'm not sure how that would fit into the full routine. Here's the full code:


Private Sub CommandButton3_Click()

'Starting with Activecell after user selects it...Cut and Paste the info (COMPANY NAME) into column (A)
Selection.Cut
Range("A" & LastRow + 1).Paste
'Cut and Paste the info (ADDRESS 1 row down) to end of column (B)
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Selection.Cut
Range("C" & LastRow + 1).Paste
' Cut and Paste the info (PHONE NUMBER 3 more rows down) to end of column (F)
ActiveCell.Offset(rowOffset:=3, columnOffset:=0).Activate
Selection.Cut
Range("F" & LastRow + 1).Paste
' Cut and Divide the info (CITY, STATE, AND ZIP) to end of columns (B), (D), and (E)
ActiveCell.Offset(rowOffset:=-2, columnOffset:=0).Activate

With ActiveCell
zipStr = Right(CStr(.Value), 5)
' Tests to see if the zip is thruely the last five digits before Cut/Paste
If zipStr Like "#####" Then
' Paste Zip into Column (F)
Range("E" & LastRow + 1).Paste
' Trim zipcode and extra space off of activecell
.Value = Left(.Value, Len(.Value) - 6)
' Now, cut State off activecell and paste at end of Column (D)
stateStr = Right(CStr(.Value), 2)
.Value = Left(.Value, Len(.Value) - 4)
Range("D" & LastRow + 1).Paste
' THIS WHOLE NEXT "ELSE" JUST CLIPS THE EXTRA SPACE BEFORE DUPLICATING THE ABOVE ITEMS.
Else
.Value = Left(.Value, Len(.Value) - 1)
zipStr = Right(CStr(.Value), 5)
Range("E" & LastRow + 1).Paste
' Trim zipcode and extra space off of activecell
.Value = Left(.Value, Len(.Value) - 6)
' Now, cut State off activecell and paste at end of Column (D)
stateStr = Right(CStr(.Value), 2)
.Value = Left(.Value, Len(.Value) - 4)
Range("D" & LastRow + 1).Paste

End If
End With
End Sub


How would that fit into the structure. I believe I used to use the "Find" method when I used to do this.

[GTO]

Hey Dan,

I'm about sacked, but for me or whoever answers, I believe a sample workbook w/your current code would be easier to "see". (I are thick-headed most all the time.)

I get that you are getting the source data from a vertical range of ce;;s, and converting to a horizontal (coerced for formatting), but it is unclear whether we're staring from the same sheet ect...

Hope to help,

Mark

[DanOfEarth]

Thanks again Mark...

Hmmm,:think:

Let me try it from a simpler angle...

I've familiar with about five different ways to find which cell is the last cell in a particular row. My problem is the syntax of PASTING "copied" info into that cell.

For instance, another way to find the cell is:


Dim AddressRow As Long
With ActiveSheet
AddressRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


But how do I simply paste info into "AddressRow" from my activecell:

AddressRow.Paste ??

Does that make since? I've been up for two days and my brain is in a blender.

[GTO]

Pretty much like my first response, though I goobered up missing that you wanted to Cut/Paste rather than just copy. Using Selection (which could be more than one cell), something like:
Sub CutPaste()
Dim lRow As Long

lRow = Cells(65536, 1).End(xlUp).Row + 1
Selection.Cut


ActiveSheet.Paste Range("A" & lRow)
End Sub
or ActiveCell:
Sub CutPaste()
Dim lRow As Long

lRow = Cells(65536, 1).End(xlUp).Row + 1
ActiveCell.Cut


ActiveSheet.Paste Range("A" & lRow)
End Sub

Is that better?

In answer to that last question, 'AddressRow.Paste' wouldn't work, as 'AddressRow' is only the Row number. Now that I'm staring (thru bleary eyes) at your code a bit better, I think your glitch was failing to specify the worksheet (if only thru ActiveSheet, such as in the examples). If you don't need to Cut the info, the copy example should be quicker...

Attached file.

Hope that helps?

Mark

[GTO]

My apologies. I just noticed your join date, and would like to say Welcome! You will 'meet' lots of nice folks here, who are most helpful. It is a great site that I have learned a lot from; both as a guest for a couple of years and a member for the past few months. Again, welcome, and hope to be of help. Mark

[DanOfEarth]

Awesome. Thanks Mark.

And thanks for the welcome. Everybody has been really cool so far. I've been out of VBA for about eight years, and have forgotten even the simplest of solutions. I actually started back in 93 when it was only a set of Macros, but I'm back for good I believe.

I'm trying your fix up as we speak. I'll post in a bit.

[DanOfEarth]

GOT IT!! Thank you!

It was a bit messier than I thought. I forgot that I was cutting this info farther down on the same sheet, so it through the "Endrow" business into disarray. That being said, I had to tweak what you gave me but it worked.

It kept flagging the line:
Activesheet.Paste Range("A" & lRow)

So I had to name the info, then insert via....


Dim lRow As Long
lRow = Sheets("Produce Companies").Cells(65536, 1).End(xlUp).Row + 1
'Starting with Activecell...Cut and Paste the COMPANY NAME into column (A)
CompanyStr = ActiveCell
ActiveCell.Clear
Activesheet.Range("A" & lRow) = CompanyStr


That then messed up my Activecell.cut commands, so I changed them to Activecell.Clear

But it got me there! Thank you very much. This copy/paste button is going to have lightening shooting out of it by the time I'm done with it!:wizard: