| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
What code could I use to Delete an entire row (move cells up) if the
value in A8:A40 is not London; New York, Paris or Berlin? Secondly, how do I achieve a simple Copy Paste, but always 6 rows below the last populates cell in Column A? My problem is my data changes but I always wish to paste 6 rows below the last data. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Entire Row if value is not Q
Hi Sean
Possibly Sub DeleteRows() For i = 40 to 8 Step -1 Select Case Cells(i,1).Value Case "London","New York","Paris","Berlin" Case Else Rows(i).Delete End Select Next i End Sub and for the next bit: Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) Hope this helps! Richard On 7 Feb, 08:08, "Sean" wrote: What code could I use to Delete an entire row (move cells up) if the value in A8:A40 is not London; New York, Paris or Berlin? Secondly, how do I achieve a simple Copy Paste, but always 6 rows below the last populates cell in Column A? My problem is my data changes but I always wish to paste 6 rows below the last data. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Entire Row if value is not Q
On Feb 7, 8:58 am, "RichardSchollar"
wrote: Hi Sean Possibly Sub DeleteRows() For i = 40 to 8 Step -1 Select Case Cells(i,1).Value Case "London","New York","Paris","Berlin" Case Else Rows(i).Delete End Select Next i End Sub and for the next bit: Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) Hope this helps! Richard On 7 Feb, 08:08, "Sean" wrote: What code could I use to Delete an entire row (move cells up) if the value in A8:A40 is not London; New York, Paris or Berlin? Secondly, how do I achieve a simple Copy Paste, but always 6 rows below the last populates cell in Column A? My problem is my data changes but I always wish to paste 6 rows below the last data. Thanks- Hide quoted text - - Show quoted text - Thanks Richard that seems to work a treat |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Entire Row if value is not Q
One Q Richard if my Copy and Paste are on different sheets how would I
incorporate that into Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) On Feb 7, 8:58 am, "RichardSchollar" wrote: Hi Sean Possibly Sub DeleteRows() For i = 40 to 8 Step -1 Select Case Cells(i,1).Value Case "London","New York","Paris","Berlin" Case Else Rows(i).Delete End Select Next i End Sub and for the next bit: Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) Hope this helps! Richard On 7 Feb, 08:08, "Sean" wrote: What code could I use to Delete an entire row (move cells up) if the value in A8:A40 is not London; New York, Paris or Berlin? Secondly, how do I achieve a simple Copy Paste, but always 6 rows below the last populates cell in Column A? My problem is my data changes but I always wish to paste 6 rows below the last data. Thanks- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Entire Row if value is not Q
Worksheets("Sheet1").Range("B1").Copy Destination:= _
Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message ups.com... One Q Richard if my Copy and Paste are on different sheets how would I incorporate that into Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) On Feb 7, 8:58 am, "RichardSchollar" wrote: Hi Sean Possibly Sub DeleteRows() For i = 40 to 8 Step -1 Select Case Cells(i,1).Value Case "London","New York","Paris","Berlin" Case Else Rows(i).Delete End Select Next i End Sub and for the next bit: Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) Hope this helps! Richard On 7 Feb, 08:08, "Sean" wrote: What code could I use to Delete an entire row (move cells up) if the value in A8:A40 is not London; New York, Paris or Berlin? Secondly, how do I achieve a simple Copy Paste, but always 6 rows below the last populates cell in Column A? My problem is my data changes but I always wish to paste 6 rows below the last data. Thanks- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Entire Row if value is not Q
On Feb 7, 10:08 am, "Bob Phillips" wrote:
Worksheets("Sheet1").Range("B1").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message ups.com... One Q Richard if my Copy and Paste are on different sheets how would I incorporate that into Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) On Feb 7, 8:58 am, "RichardSchollar" wrote: Hi Sean Possibly Sub DeleteRows() For i = 40 to 8 Step -1 Select Case Cells(i,1).Value Case "London","New York","Paris","Berlin" Case Else Rows(i).Delete End Select Next i End Sub and for the next bit: Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) Hope this helps! Richard On 7 Feb, 08:08, "Sean" wrote: What code could I use to Delete an entire row (move cells up) if the value in A8:A40 is not London; New York, Paris or Berlin? Secondly, how do I achieve a simple Copy Paste, but always 6 rows below the last populates cell in Column A? My problem is my data changes but I always wish to paste 6 rows below the last data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Thanks Bob, if I was just to Paste values and Formats within the formula how would I incorporate? Worksheets("Sheet1").Range("B1").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6) I've tried Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False But not sure where to place it |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Entire Row if value is not Q
Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).Value = _ Worksheets("Sheet1").Range("B1").Value Mike F "Sean" wrote in message oups.com... On Feb 7, 10:08 am, "Bob Phillips" wrote: Worksheets("Sheet1").Range("B1").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message ups.com... One Q Richard if my Copy and Paste are on different sheets how would I incorporate that into Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) On Feb 7, 8:58 am, "RichardSchollar" wrote: Hi Sean Possibly Sub DeleteRows() For i = 40 to 8 Step -1 Select Case Cells(i,1).Value Case "London","New York","Paris","Berlin" Case Else Rows(i).Delete End Select Next i End Sub and for the next bit: Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) Hope this helps! Richard On 7 Feb, 08:08, "Sean" wrote: What code could I use to Delete an entire row (move cells up) if the value in A8:A40 is not London; New York, Paris or Berlin? Secondly, how do I achieve a simple Copy Paste, but always 6 rows below the last populates cell in Column A? My problem is my data changes but I always wish to paste 6 rows below the last data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Thanks Bob, if I was just to Paste values and Formats within the formula how would I incorporate? Worksheets("Sheet1").Range("B1").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6) I've tried Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False But not sure where to place it |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete Entire Row if value is not Q
No need to select
Worksheets("Sheet1").Range("B1").Copy Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial Paste:=xlPasteValues -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message oups.com... On Feb 7, 10:08 am, "Bob Phillips" wrote: Worksheets("Sheet1").Range("B1").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message ups.com... One Q Richard if my Copy and Paste are on different sheets how would I incorporate that into Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) On Feb 7, 8:58 am, "RichardSchollar" wrote: Hi Sean Possibly Sub DeleteRows() For i = 40 to 8 Step -1 Select Case Cells(i,1).Value Case "London","New York","Paris","Berlin" Case Else Rows(i).Delete End Select Next i End Sub and for the next bit: Range("B1").Copy Destination:=Range("A65536").End(xlUp).Offset(6) Hope this helps! Richard On 7 Feb, 08:08, "Sean" wrote: What code could I use to Delete an entire row (move cells up) if the value in A8:A40 is not London; New York, Paris or Berlin? Secondly, how do I achieve a simple Copy Paste, but always 6 rows below the last populates cell in Column A? My problem is my data changes but I always wish to paste 6 rows below the last data. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Thanks Bob, if I was just to Paste values and Formats within the formula how would I incorporate? Worksheets("Sheet1").Range("B1").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6) I've tried Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False But not sure where to place it |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete Entire Row | Excel Programming | |||
| Delete entire row if | Excel Discussion (Misc queries) | |||
| Need to delete last value in column but not delete entire row | Excel Programming | |||
| Delete Entire Row If Q | Excel Programming | |||
| Another delete entire row | Excel Programming | |||
Hybrid Mode
