| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows where entry in column A
Hi
I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy rows where entry in column A
Just to modify this - the test for column A only needs to extend from row 2
to row 100. So if, in those set of rows, there is a value in column A then copy the antire row (values only) to another sheet. James "access user" wrote: Hi I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy rows where entry in column A
Alt + F11 to open VB editor. Right click 'This workbook' and insert module.
Paste this in and run it Sub stance() Dim myrange, copyrange As Range Sheets("Sheet1").Select Set myrange = Range("A2:A100") For Each c In myrange If c.Value < "" Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next copyrange.Copy Sheets("Sheet2").Select 'Change to suit Cells(1, 1).Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Did that work? Mike "access user" wrote: Hi I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy rows where entry in column A
Hi
I get 'object variable or With block variable not set error' on line copyrange.copy tia James "Mike H" wrote: Alt + F11 to open VB editor. Right click 'This workbook' and insert module. Paste this in and run it Sub stance() Dim myrange, copyrange As Range Sheets("Sheet1").Select Set myrange = Range("A2:A100") For Each c In myrange If c.Value < "" Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next copyrange.Copy Sheets("Sheet2").Select 'Change to suit Cells(1, 1).Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Did that work? Mike "access user" wrote: Hi I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy rows where entry in column A
Thats because it's not finding any cells with data in so there is nothing for
it to copy. I never bothered to trap for that becuase I assumed incorrectly there would always be something. You can trap for that with If Not copyrange Is Nothing Then copyrange.Copy Else Exit Sub End If Mike "access user" wrote: Hi I get 'object variable or With block variable not set error' on line copyrange.copy tia James "Mike H" wrote: Alt + F11 to open VB editor. Right click 'This workbook' and insert module. Paste this in and run it Sub stance() Dim myrange, copyrange As Range Sheets("Sheet1").Select Set myrange = Range("A2:A100") For Each c In myrange If c.Value < "" Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next copyrange.Copy Sheets("Sheet2").Select 'Change to suit Cells(1, 1).Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Did that work? Mike "access user" wrote: Hi I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy rows where entry in column A
You deserve a pint of Guiness because you are 'pure Genius' and it's St
Patrick's Day. Thanks a lot. James "Mike H" wrote: Thats because it's not finding any cells with data in so there is nothing for it to copy. I never bothered to trap for that becuase I assumed incorrectly there would always be something. You can trap for that with If Not copyrange Is Nothing Then copyrange.Copy Else Exit Sub End If Mike "access user" wrote: Hi I get 'object variable or With block variable not set error' on line copyrange.copy tia James "Mike H" wrote: Alt + F11 to open VB editor. Right click 'This workbook' and insert module. Paste this in and run it Sub stance() Dim myrange, copyrange As Range Sheets("Sheet1").Select Set myrange = Range("A2:A100") For Each c In myrange If c.Value < "" Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next copyrange.Copy Sheets("Sheet2").Select 'Change to suit Cells(1, 1).Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Did that work? Mike "access user" wrote: Hi I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy rows where entry in column A
Thank's for that but three small points:-
I'm not a genius far from it. Joel's solution is better than mine I'm not going to enter the debate about when St Patricks day is this year because the Pope seems to have quite strong views in the matter<g Mike "access user" wrote: You deserve a pint of Guiness because you are 'pure Genius' and it's St Patrick's Day. Thanks a lot. James "Mike H" wrote: Thats because it's not finding any cells with data in so there is nothing for it to copy. I never bothered to trap for that becuase I assumed incorrectly there would always be something. You can trap for that with If Not copyrange Is Nothing Then copyrange.Copy Else Exit Sub End If Mike "access user" wrote: Hi I get 'object variable or With block variable not set error' on line copyrange.copy tia James "Mike H" wrote: Alt + F11 to open VB editor. Right click 'This workbook' and insert module. Paste this in and run it Sub stance() Dim myrange, copyrange As Range Sheets("Sheet1").Select Set myrange = Range("A2:A100") For Each c In myrange If c.Value < "" Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next copyrange.Copy Sheets("Sheet2").Select 'Change to suit Cells(1, 1).Select Selection.PasteSpecial Paste:=xlPasteValues End Sub Did that work? Mike "access user" wrote: Hi I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy rows where entry in column A
Your original request
Sheets("Sheet1").Cells.SpecialCells(xlTextValues). EntireRow.Copy Sheets("Sheet2").Cells.PasteSpecial Paste:=xlPasteValues fror a limited range Sheets("Sheet1").Range("A1:A100").SpecialCells(xlT extValues).EntireRow.Copy Sheets("Sheet2").Cells.PasteSpecial Paste:=xlPasteValues "access user" wrote: Hi I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy rows where entry in column A
Thanks Joel, Mike H got there first. Appreciate your response though.
James "access user" wrote: Hi I wish to copy only rows where there is an entry in column A. For those rows, I wish to copy the entire row (values only) to another sheet. tia James |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy last entry of column to bottom of column | Excel Worksheet Functions | |||
| Excel: How copy all rows that have a given column entry? | Excel Discussion (Misc queries) | |||
| copy value of cell from last entry in column | Excel Worksheet Functions | |||
| Auto-copy the last entry in a column to a new cell | Excel Worksheet Functions | |||
| Insert rows depending on entry in certain column | Excel Programming | |||
Hybrid Mode
