Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select rows in pivot table with macro | Excel Discussion (Misc queries) | |||
How can i randomly select 780 rows from 4000 rows of data | Excel Worksheet Functions | |||
Select certain data rows | New Users to Excel | |||
Want macro to select & copy cells from a different worksheet | Excel Discussion (Misc queries) | |||
Select alternate rows to copy | Excel Discussion (Misc queries) |