Copy rows containing data & paste in first blank row

A

Ali Kırksekiz

Board Regular

Joined

Jul 20, 2006

Messages

108

• Sep 6, 2006

• #2

(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.

Click to expand...

Code:

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

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

Click to expand...

For A column

Code:

Sub Find()
Sheets("Data").Select
Selection.SpecialCells(xlBlanks).Areas(1).Cells(1).Select
End Sub

 

Upvote 0

R

redhots4

Board Regular

Joined

Aug 30, 2004

Messages

136

Office Version

1. 365

Platform

1. MacOS

• Sep 6, 2006

• #3

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

R

redhots4

Board Regular

Joined

Aug 30, 2004

Messages

136

Office Version

1. 365

Platform

1. MacOS

• Sep 6, 2006

• #4

Can anyone else help me with this issue? Thanks in advance!!

 

Upvote 0

A

Ali Kırksekiz

Board Regular

Joined

Jul 20, 2006

Messages

108

• Sep 7, 2006

• #5

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

erik.van.geit

MrExcel MVP

Joined

Feb 1, 2003

Messages

17,832

• Sep 7, 2006

• #6

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?

Click to expand...

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

R

redhots4

Board Regular

Joined

Aug 30, 2004

Messages

136

Office Version

1. 365

Platform

1. MacOS

• Sep 15, 2006

• #7

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

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Sep 15, 2006

• #8

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

R

redhots4

Board Regular

Joined

Aug 30, 2004

Messages

136

Office Version

1. 365

Platform

1. MacOS

• Sep 15, 2006

• #9

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

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Sep 15, 2006

• #10

Well it works for me.

You do have data in column A?

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

 

Upvote 0