macro to select and copy rows only containing data
 

All

Have searched various forums and can't seem to find what I need. Is really
simple and I know can be done but...

I need to be able to select all rows containing data and then copy and paste
to new sheet - would like the code that selects these rows only.

TIA

Shaz

macro to select and copy rows only containing data
 

Here is a crude macro without any error checking..
===============
Sub CopyNonBlankRows()

Dim i As Long
Dim j As Long
Dim dest As String
Dim src As String

j = 1
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
' Change 10 to your last row or max row possible
For i = 1 To 10
' Change X to the last column or max cols possible
src = "A" & i & ":X" & i
'Assumptions - Sheet1 contain the data and Sheet2 is the destination sheet
Sheets("Sheet1").Activate
ActiveSheet.Range(src).Select
If (WorksheetFunction.CountA(Selection.Rows(1)) 0) Then
ActiveSheet.Rows(i).EntireRow.Select
Selection.Copy
dest = "A" & j
Sheets("Sheet2").Activate
ActiveSheet.Range(dest).Select
ActiveSheet.Paste
j = j + 1
End If
Next i
..Calculation = xlCalculationAutomatic
..ScreenUpdating = True
End With

End Sub

===============

"shaz0503" wrote:

All

Have searched various forums and can't seem to find what I need. Is really
simple and I know can be done but...

I need to be able to select all rows containing data and then copy and paste
to new sheet - would like the code that selects these rows only.

TIA

Shaz

macro to select and copy rows only containing data
 

Do you want a macro?

Record yourself autofiltering for non-blanks and copying to the other sheet.


Gord Dibben MS Excel MVP

On Wed, 8 Oct 2008 20:09:01 -0700, shaz0503
wrote:

All

Have searched various forums and can't seem to find what I need. Is really
simple and I know can be done but...

I need to be able to select all rows containing data and then copy and paste
to new sheet - would like the code that selects these rows only.

TIA

Shaz

macro to select and copy rows only containing data
 

Thanks for that - but I don't think I explained myself properly

I have a worksheet that contains raw data - as the number of rows of data
changes monthly I need to be able to select all rows with data only and then
I can continue with my formatting etc.

the macro I am thinking of pretty mch goes down all rows til the next blank
row and then copies all rows 'up'... hope this is a little clearer

TIA

Shaz

"shaz0503" wrote:

All

Have searched various forums and can't seem to find what I need. Is really
simple and I know can be done but...

I need to be able to select all rows containing data and then copy and paste
to new sheet - would like the code that selects these rows only.

TIA

Shaz

macro to select and copy rows only containing data
 

That's what autofilter on non-blanks would do.

Filter out all rows with no data leaving you with just data rows for
copying.


Gord

On Thu, 9 Oct 2008 17:32:01 -0700, shaz0503
wrote:

Thanks for that - but I don't think I explained myself properly

I have a worksheet that contains raw data - as the number of rows of data
changes monthly I need to be able to select all rows with data only and then
I can continue with my formatting etc.

the macro I am thinking of pretty mch goes down all rows til the next blank
row and then copies all rows 'up'... hope this is a little clearer

TIA

Shaz

"shaz0503" wrote:

All

Have searched various forums and can't seem to find what I need. Is really
simple and I know can be done but...

I need to be able to select all rows containing data and then copy and paste
to new sheet - would like the code that selects these rows only.

TIA

Shaz

macro to select and copy rows only containing data
 

Thanks Gord

I am trying to make this workbook as auto as poss. I did want to select
only the rows with data to then 'insert copied cells' at the top of a
worksheet to update YTD information _ to allow the most recent at the top....

....but i will try and incorp autofilter in


rgds

Shaz


"Gord Dibben" wrote:

That's what autofilter on non-blanks would do.

Filter out all rows with no data leaving you with just data rows for
copying.


Gord

On Thu, 9 Oct 2008 17:32:01 -0700, shaz0503
wrote:

Thanks for that - but I don't think I explained myself properly

I have a worksheet that contains raw data - as the number of rows of data
changes monthly I need to be able to select all rows with data only and then
I can continue with my formatting etc.

the macro I am thinking of pretty mch goes down all rows til the next blank
row and then copies all rows 'up'... hope this is a little clearer

TIA

Shaz

"shaz0503" wrote:

All

Have searched various forums and can't seem to find what I need. Is really
simple and I know can be done but...

I need to be able to select all rows containing data and then copy and paste
to new sheet - would like the code that selects these rows only.

TIA

Shaz