Copy rows containing data & paste in first blank row

redhots4

Board Regular
Joined
Aug 30, 2004
Messages
136
Office Version
  1. 365
Platform
  1. MacOS
(1.) What is the VB code to select only rows containing data? Start in row B and end in Row X where X is the last row containing data.

(2.) Once item 1 above is satisfied, I need to go to another sheet ("Data"), find the first blank row, below all existing data and paste the data copied in step 1.

Thanks in advance. I'm ready to pull my hair out over here. :devilish:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
(1.) What is the VB code to select only rows containing data? Start in row B and end in Row X where X is the last row containing data.

Code:
Sub columnselect()
Range("B:B").SpecialCells(xlCellTypeConstants).Select
End Sub

(another sheet ("Data"), find the first blank row

For A column

Code:
Sub Find()
Sheets("Data").Select
Selection.SpecialCells(xlBlanks).Areas(1).Cells(1).Select
End Sub
 
Upvote 0
I'm not seeing that this code is doing what I want. Can you please re-read my post and make sure you're giving me an answer to the right question?
 
Upvote 0
Can anyone else help me with this issue? Thanks in advance!!
 
Upvote 0
Try this

You must copy these codes in Sheet1 code page

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Worksheets("Sheet1").Cells(65536, 2).End(xlUp).Address Then
    Worksheets("Data").Cells(65536, 2).End(xlUp).Offset(1, 0).Value = Target.Value
    Worksheets("Data").Cells(65536, 2).End(xlUp).Offset(1, 1).Value = Target.Offset(0, 1).Value
End If
End Sub
 
Upvote 0
I'm not seeing that this code is doing what I want. Can you please re-read my post and make sure you're giving me an answer to the right question?
Hi,

perhaps you could rephrase your question
to my sense it can be interpreted in different ways

a little example would help

kind regards,
Erik
 
Upvote 0
More information on copy / paste question

My situation, specifically, is this:

1. Info is stored on sheet "Paste" in 5 columns, A:E
2. Row 1 contains headings in "Paste" A1:E1
3. Need to copy data contained in "Paste" Columns A:E, starting in row 2 (A2) and going down to the final row containing data (Might be row 82, might be row 2475, depending upon the amount of data for the report run.)
4. Once copied, data needs to be pasted on "Data" sheet starting in the first blank row below the data already on that page. Find the first blank row, go to column A and paste.

I hope this is enough information.

Thanks!
 
Upvote 0
redhots4

Try this.
Code:
With Sheets("Paste")
    .Range("A2:E" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
 
Upvote 0
Problem

Code is choking on the second '.Range' below. (?)

With Sheets("Paste")
.Range("A2:E" & .Range ("A" & Rows.Count).End(xlUp).Row).Copy Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
 
Upvote 0
Well it works for me.:)

You do have data in column A?

By the way, how exactly is it 'choking'?
 
Upvote 0

Forum statistics

Threads
1,217,382
Messages
6,136,234
Members
450,000
Latest member
jgp19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top