Copy rows containing data & paste in first blank row

redhots4

Board Regular
Joined
Aug 30, 2004
Messages
107
(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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Ali Kırksekiz

Board Regular
Joined
Jul 20, 2006
Messages
108
(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
 

redhots4

Board Regular
Joined
Aug 30, 2004
Messages
107
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?
 

Ali Kırksekiz

Board Regular
Joined
Jul 20, 2006
Messages
108

ADVERTISEMENT

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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

redhots4

Board Regular
Joined
Aug 30, 2004
Messages
107

ADVERTISEMENT

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!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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
 

redhots4

Board Regular
Joined
Aug 30, 2004
Messages
107
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Well it works for me.:)

You do have data in column A?

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

Forum statistics

Threads
1,137,296
Messages
5,680,672
Members
419,924
Latest member
Dhamodharan992

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
Top