ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Got to end of sheet - first column (https://www.excelbanter.com/excel-discussion-misc-queries/195436-got-end-sheet-first-column.html)

Bonnie
Got to end of sheet - first column
 
I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.

Mike H
Got to end of sheet - first column
 
Bonnie,

You posted in general questions but the use of XLlastCell makes me think you
want a VB solution so try this

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

Mike

"Bonnie" wrote:

I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.

Bonnie
Got to end of sheet - first column
 
Your right Mike. Sorry.

I'll give this a try.

Thanks so much!

"Mike H" wrote:

Bonnie,

You posted in general questions but the use of XLlastCell makes me think you
want a VB solution so try this

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

Mike

"Bonnie" wrote:

I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.

Bonnie
Got to end of sheet - first column
 
I'm almost there Mike. But I need a little more help please.

Here's what I have:

Sheets("MASTER").Select
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select

Selection.Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

I am getting the error: The information can't be pasted because the coy
area and the paste area are not the same size?

"Mike H" wrote:

Bonnie,

You posted in general questions but the use of XLlastCell makes me think you
want a VB solution so try this

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

Mike

"Bonnie" wrote:

I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.

Mike H
Got to end of sheet - first column
 
Hi,

This works for me. the only comment I would make is why do you have the
offset in the copyrange, all it does is copy an empty cell.

Sub askMe()
Sheets("Master").Select
Sheets("MASTER").Range("A1", Range("A" &
Rows.Count).End(xlUp).Offset(1)).Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
End Sub

Mike

"Bonnie" wrote:

I'm almost there Mike. But I need a little more help please.

Here's what I have:

Sheets("MASTER").Select
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select

Selection.Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

I am getting the error: The information can't be pasted because the coy
area and the paste area are not the same size?

"Mike H" wrote:

Bonnie,

You posted in general questions but the use of XLlastCell makes me think you
want a VB solution so try this

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

Mike

"Bonnie" wrote:

I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.

Bonnie
Got to end of sheet - first column
 
Just because I was going to come back and delete that range so I grabbed the
empty row and it doesn't hurt my paste.

Here's the problem I have - the copy range is copying all the way to the
bottom of the sheet and I just want to copy until the first blank row. There
are blank rows between each record. A record is in so many rows until the
blank row.

Like this:

Name
address1
city
phone

Name
address1
address2
city
phone
email

Thanks in advance,

Bonnie

"Mike H" wrote:

Hi,

This works for me. the only comment I would make is why do you have the
offset in the copyrange, all it does is copy an empty cell.

Sub askMe()
Sheets("Master").Select
Sheets("MASTER").Range("A1", Range("A" &
Rows.Count).End(xlUp).Offset(1)).Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
End Sub

Mike

"Bonnie" wrote:

I'm almost there Mike. But I need a little more help please.

Here's what I have:

Sheets("MASTER").Select
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select

Selection.Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

I am getting the error: The information can't be pasted because the coy
area and the paste area are not the same size?

"Mike H" wrote:

Bonnie,

You posted in general questions but the use of XLlastCell makes me think you
want a VB solution so try this

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

Mike

"Bonnie" wrote:

I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.

Mike H
Got to end of sheet - first column
 
Hi,

Use XLDown

Sub askMe()
Sheets("Master").Select
lastrow = Range("A1").End(xlDown).Row
Sheets("MASTER").Range("A1:A" & lastrow).Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Stop
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
End Sub

"Bonnie" wrote:

Just because I was going to come back and delete that range so I grabbed the
empty row and it doesn't hurt my paste.

Here's the problem I have - the copy range is copying all the way to the
bottom of the sheet and I just want to copy until the first blank row. There
are blank rows between each record. A record is in so many rows until the
blank row.

Like this:

Name
address1
city
phone

Name
address1
address2
city
phone
email

Thanks in advance,

Bonnie

"Mike H" wrote:

Hi,

This works for me. the only comment I would make is why do you have the
offset in the copyrange, all it does is copy an empty cell.

Sub askMe()
Sheets("Master").Select
Sheets("MASTER").Range("A1", Range("A" &
Rows.Count).End(xlUp).Offset(1)).Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
End Sub

Mike

"Bonnie" wrote:

I'm almost there Mike. But I need a little more help please.

Here's what I have:

Sheets("MASTER").Select
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select

Selection.Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

I am getting the error: The information can't be pasted because the coy
area and the paste area are not the same size?

"Mike H" wrote:

Bonnie,

You posted in general questions but the use of XLlastCell makes me think you
want a VB solution so try this

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

Mike

"Bonnie" wrote:

I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.

Bonnie
Got to end of sheet - first column
 
Thanks Mike - I didn't mean to but I got to threads going and also have a
suggestion from Otto. I'll try them and let you know.

Thanks again,

Bonnie

"Mike H" wrote:

Hi,

Use XLDown

Sub askMe()
Sheets("Master").Select
lastrow = Range("A1").End(xlDown).Row
Sheets("MASTER").Range("A1:A" & lastrow).Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Stop
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
End Sub

"Bonnie" wrote:

Just because I was going to come back and delete that range so I grabbed the
empty row and it doesn't hurt my paste.

Here's the problem I have - the copy range is copying all the way to the
bottom of the sheet and I just want to copy until the first blank row. There
are blank rows between each record. A record is in so many rows until the
blank row.

Like this:

Name
address1
city
phone

Name
address1
address2
city
phone
email

Thanks in advance,

Bonnie

"Mike H" wrote:

Hi,

This works for me. the only comment I would make is why do you have the
offset in the copyrange, all it does is copy an empty cell.

Sub askMe()
Sheets("Master").Select
Sheets("MASTER").Range("A1", Range("A" &
Rows.Count).End(xlUp).Offset(1)).Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
End Sub

Mike

"Bonnie" wrote:

I'm almost there Mike. But I need a little more help please.

Here's what I have:

Sheets("MASTER").Select
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select

Selection.Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

I am getting the error: The information can't be pasted because the coy
area and the paste area are not the same size?

"Mike H" wrote:

Bonnie,

You posted in general questions but the use of XLlastCell makes me think you
want a VB solution so try this

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

Mike

"Bonnie" wrote:

I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.

Bonnie
Got to end of sheet - first column
 
""
Hi Mike,

I'm getting an error: "Argument Not Optional."

Bonnie

"Mike H" wrote:

Hi,

Use XLDown

Sub askMe()
Sheets("Master").Select
lastrow = Range("A1").End(xlDown).Row
Sheets("MASTER").Range("A1:A" & lastrow).Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Stop
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
End Sub

"Bonnie" wrote:

Just because I was going to come back and delete that range so I grabbed the
empty row and it doesn't hurt my paste.

Here's the problem I have - the copy range is copying all the way to the
bottom of the sheet and I just want to copy until the first blank row. There
are blank rows between each record. A record is in so many rows until the
blank row.

Like this:

Name
address1
city
phone

Name
address1
address2
city
phone
email

Thanks in advance,

Bonnie

"Mike H" wrote:

Hi,

This works for me. the only comment I would make is why do you have the
offset in the copyrange, all it does is copy an empty cell.

Sub askMe()
Sheets("Master").Select
Sheets("MASTER").Range("A1", Range("A" &
Rows.Count).End(xlUp).Offset(1)).Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
End Sub

Mike

"Bonnie" wrote:

I'm almost there Mike. But I need a little more help please.

Here's what I have:

Sheets("MASTER").Select
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(1)).Select

Selection.Copy
Sheets("FINAL").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

I am getting the error: The information can't be pasted because the coy
area and the paste area are not the same size?

"Mike H" wrote:

Bonnie,

You posted in general questions but the use of XLlastCell makes me think you
want a VB solution so try this

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

Mike

"Bonnie" wrote:

I know how to get to XLlastCell but want to end up on the last row, first
column.

Thanks in advance.


All times are GMT +1. The time now is 02:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com