I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

Is the bottom of the spreadsheet ragged. That is to say each column stops at
a different row number. If so then do you want to go to

1. the first empty cell at the bottom of each column

2. or do you want to go to the first row where no column contains any data?
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

Try this. It will select the first cell in the first row that is totally
empty:

Sub Macro1()
Dim j As Long
Dim i As Long
Dim r, rr As Range
Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub

--
Gary''s Student


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

This is another way to get the last row. It is a bit faster as it does not
iterate one cell at a time.

Dim lngLastRow As Long

with sheets("Sheet1")
On Error Resume Next
.select
lngLastRow = .Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On error Goto 0
.cells(lnglastrow + 1, "A").select
end with
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Try this. It will select the first cell in the first row that is totally
empty:

Sub Macro1()
Dim j As Long
Dim i As Long
Dim r, rr As Range
Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub

--
Gary''s Student


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

Sorry one goof in that code... here is the fix...

Dim lngLastRow As Long

with sheets("Sheet1")
On Error Resume Next
.select
lngLastRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On error Goto 0
.cells(lnglastrow + 1, "A").select
end with

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

This is another way to get the last row. It is a bit faster as it does not
iterate one cell at a time.

Dim lngLastRow As Long

with sheets("Sheet1")
On Error Resume Next
.select
lngLastRow = .Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On error Goto 0
.cells(lnglastrow + 1, "A").select
end with
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Try this. It will select the first cell in the first row that is totally
empty:

Sub Macro1()
Dim j As Long
Dim i As Long
Dim r, rr As Range
Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub

--
Gary''s Student


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

The iteration is there just in case there is an empty row embedded somewhere
in UsedRange. If there are no internal empties, then:

j = rr.Rows.Count + rr.Row

will go one row past UsedRange and insure finding something (I hope)
--
Gary's Student


"Jim Thomlinson" wrote:

This is another way to get the last row. It is a bit faster as it does not
iterate one cell at a time.

Dim lngLastRow As Long

with sheets("Sheet1")
On Error Resume Next
.select
lngLastRow = .Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On error Goto 0
.cells(lnglastrow + 1, "A").select
end with
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Try this. It will select the first cell in the first row that is totally
empty:

Sub Macro1()
Dim j As Long
Dim i As Long
Dim r, rr As Range
Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub

--
Gary''s Student


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

Thanks Jim. The bottom is ragged adn I'm looking for opt 2 as you have below.

"Jim Thomlinson" wrote:

Is the bottom of the spreadsheet ragged. That is to say each column stops at
a different row number. If so then do you want to go to

1. the first empty cell at the bottom of each column

2. or do you want to go to the first row where no column contains any data?
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

That will find the first blank row potentially embedded in a list of data.
There may be filled rows below that row, so you need to ensure that you don't
assume that the rows beneath what was found are empty. But fair enough. Yours
will fill in the blank emebedded rows where mine will not. It will find the
very last row that had anything in it. Depends what the OP wants to do.
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

The iteration is there just in case there is an empty row embedded somewhere
in UsedRange. If there are no internal empties, then:

j = rr.Rows.Count + rr.Row

will go one row past UsedRange and insure finding something (I hope)
--
Gary's Student


"Jim Thomlinson" wrote:

This is another way to get the last row. It is a bit faster as it does not
iterate one cell at a time.

Dim lngLastRow As Long

with sheets("Sheet1")
On Error Resume Next
.select
lngLastRow = .Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On error Goto 0
.cells(lnglastrow + 1, "A").select
end with
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Try this. It will select the first cell in the first row that is totally
empty:

Sub Macro1()
Dim j As Long
Dim i As Long
Dim r, rr As Range
Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub

--
Gary''s Student


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

Gary and I have had a fun time with this one. Assuming that you want to fill
in any blank rows that may exits in your data, go with Gary's. Note you will
need to paste data in one row at a time and run his sub prior to each paste.
If however you just want to find the last row where anything is populated, I
would be more inclined to go with my code. You won't have to worry about
potentialy overwriting any existing data... It's up to you...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

Thanks Jim. The bottom is ragged adn I'm looking for opt 2 as you have below.

"Jim Thomlinson" wrote:

Is the bottom of the spreadsheet ragged. That is to say each column stops at
a different row number. If so then do you want to go to

1. the first empty cell at the bottom of each column

2. or do you want to go to the first row where no column contains any data?
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.

Jim & Gary: You guys are great. I am using Jim's code as it does give me what
I need. Gary, I'm sure I'll be looking for more code sometime soon so you
will have another opportunity to leave your mark with me.

I really don't like asking for help but you two made it very easy and a
great experience.

Have a FANTASTIC weekend!

Sheldon

"Jim Thomlinson" wrote:

That will find the first blank row potentially embedded in a list of data.
There may be filled rows below that row, so you need to ensure that you don't
assume that the rows beneath what was found are empty. But fair enough. Yours
will fill in the blank emebedded rows where mine will not. It will find the
very last row that had anything in it. Depends what the OP wants to do.
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

The iteration is there just in case there is an empty row embedded somewhere
in UsedRange. If there are no internal empties, then:

j = rr.Rows.Count + rr.Row

will go one row past UsedRange and insure finding something (I hope)
--
Gary's Student


"Jim Thomlinson" wrote:

This is another way to get the last row. It is a bit faster as it does not
iterate one cell at a time.

Dim lngLastRow As Long

with sheets("Sheet1")
On Error Resume Next
.select
lngLastRow = .Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On error Goto 0
.cells(lnglastrow + 1, "A").select
end with
--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

Try this. It will select the first cell in the first row that is totally
empty:

Sub Macro1()
Dim j As Long
Dim i As Long
Dim r, rr As Range
Set rr = ActiveSheet.UsedRange

j = rr.Rows.Count + rr.Row
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub

--
Gary''s Student


"Sheldon" wrote:

That's great help Jim. How can I get this to look at columns A-Q?

"Jim Thomlinson" wrote:

With Sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlup).offset(1,0).select
end with

This will select the first blank row looking at the items in column A...
--
HTH...

Jim Thomlinson


"Sheldon" wrote:

I'm writing a Macro that copies information from several worksheets to one
worksheet and I need the next block of data to be pasted in the first row
that is empty. Can someone help me with some VB code that will select a cell
in the first empty row?

Thanks for any assistance.