Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 364
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select rows in pivot table with macro Dolphinv4 Excel Discussion (Misc queries) 1 April 22nd 08 06:21 PM
How can i randomly select 780 rows from 4000 rows of data bbb Excel Worksheet Functions 2 July 6th 07 08:21 PM
Select certain data rows lee's news New Users to Excel 2 December 19th 06 10:19 PM
Want macro to select & copy cells from a different worksheet RocketRod Excel Discussion (Misc queries) 5 February 28th 06 01:53 PM
Select alternate rows to copy Christina Excel Discussion (Misc queries) 4 January 27th 05 02:05 AM


All times are GMT +1. The time now is 09:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"