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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
On Feb 7, 12:02 pm, "Bob Phillips" wrote:
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 roups.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- Hide quoted text - - Show quoted text - Bob, am I missing something her on this I'm getting a 'expected expression' message on the very last line Worksheets("Sheet1").Range("A1:O39").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial Paste:=xlPasteValues |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
Perhaps a word-wrap problem;
after typing the .PasteSpecial (alter the last character "l" a space + the underscore character, then the return key so that the last line(s) look like this: Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial _ Paste:=xlPasteValues "Sean" wrote in message oups.com: On Feb 7, 12:02 pm, "Bob Phillips" wrote: 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 roups.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- Hide quoted text - - Show quoted text - Bob, am I missing something her on this I'm getting a 'expected expression' message on the very last line Worksheets("Sheet1").Range("A1:O39").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial Paste:=xlPasteValues |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
damn, "after the last character", not "alter the last character" LOL
"JMay" wrote in message : Perhaps a word-wrap problem; after typing the .PasteSpecial (alter the last character "l" a space + the underscore character, then the return key so that the last line(s) look like this: Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial _ Paste:=xlPasteValues "Sean" wrote in message oups.com: On Feb 7, 12:02 pm, "Bob Phillips" wrote: 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 roups.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- Hide quoted text - - Show quoted text - Bob, am I missing something her on this I'm getting a 'expected expression' message on the very last line Worksheets("Sheet1").Range("A1:O39").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial Paste:=xlPasteValues |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
On Feb 7, 12:39 pm, "JMay" wrote:
damn, "after the last character", not "alter the last character" LOL "JMay" wrote in message : Perhaps a word-wrap problem; after typing the .PasteSpecial (alter the last character "l" a space + the underscore character, then the return key so that the last line(s) look like this: Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial _ Paste:=xlPasteValues "Sean" wrote in message roups.com: On Feb 7, 12:02 pm, "Bob Phillips" wrote: 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 roups.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 roups.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- Hide quoted text - - Show quoted text - Bob, am I missing something her on this I'm getting a 'expected expression' message on the very last line Worksheets("Sheet1").Range("A1:O39").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial Paste:=xlPasteValues- Hide quoted text - - Show quoted text - No good JMay, still same message |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
Post the whole code section Sean.
-- --- 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, 12:39 pm, "JMay" wrote: damn, "after the last character", not "alter the last character" LOL "JMay" wrote in message : Perhaps a word-wrap problem; after typing the .PasteSpecial (alter the last character "l" a space + the underscore character, then the return key so that the last line(s) look like this: Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial _ Paste:=xlPasteValues "Sean" wrote in message roups.com: On Feb 7, 12:02 pm, "Bob Phillips" wrote: 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 roups.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 roups.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- Hide quoted text - - Show quoted text - Bob, am I missing something her on this I'm getting a 'expected expression' message on the very last line Worksheets("Sheet1").Range("A1:O39").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial Paste:=xlPasteValues- Hide quoted text - - Show quoted text - No good JMay, still same message |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
Here it is Bob
Sub FormatArea1() Application.ScreenUpdating = False Sheets("Month").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlFormats Selection.PasteSpecial Paste:=xlPasteValues ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 For i = 39 To 8 Step -1 Select Case Cells(i, 1).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Cumulative").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Worksheets("Cumulative").Range("A1:O39").Copy Destination:= _ Worksheets("Area1").Range("A65536").End(xlUp).Offs et(6).PasteSpecial _ Paste:=xlPasteValues For i = 66 To 34 Step -1 Select Case Cells(i, 2).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Month").Activate Range("A1").Select End Sub |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
Worksheets("Sheet1").Range("B1").Copy
Worksheets("Sheet2").Range("A65536") _ .End(xlUp).Offset(6).PasteSpecial _ Paste:=xlPasteValues Worksheets("Sheet2").Range("A65536") _ .End(xlUp).Offset(6).PasteSpecial _ Paste:=xlFormats -- Regards, Tom Ogilvy "Sean" wrote: On Feb 7, 12:39 pm, "JMay" wrote: damn, "after the last character", not "alter the last character" LOL "JMay" wrote in message : Perhaps a word-wrap problem; after typing the .PasteSpecial (alter the last character "l" a space + the underscore character, then the return key so that the last line(s) look like this: Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial _ Paste:=xlPasteValues "Sean" wrote in message roups.com: On Feb 7, 12:02 pm, "Bob Phillips" wrote: 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 roups.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 roups.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- Hide quoted text - - Show quoted text - Bob, am I missing something her on this I'm getting a 'expected expression' message on the very last line Worksheets("Sheet1").Range("A1:O39").Copy Destination:= _ Worksheets("Sheet2").Range("A65536").End(xlUp).Off set(6).PasteSpecial Paste:=xlPasteValues- Hide quoted text - - Show quoted text - No good JMay, still same message |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
You still had a copy Destination in there with Pastespecial.
try this Sub FormatArea1() Application.ScreenUpdating = False Sheets("Month").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlFormats Selection.PasteSpecial Paste:=xlPasteValues ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 For i = 39 To 8 Step -1 Select Case Cells(i, 1).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Cumulative").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Worksheets("Cumulative").Range("A1:O39").Copy Worksheets("Area1").Range("A65536").End(xlUp).Offs et(6).PasteSpecial Paste:=xlPasteValues For i = 66 To 34 Step -1 Select Case Cells(i, 2).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Month").Activate Range("A1").Select End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message oups.com... Here it is Bob Sub FormatArea1() Application.ScreenUpdating = False Sheets("Month").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlFormats Selection.PasteSpecial Paste:=xlPasteValues ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 For i = 39 To 8 Step -1 Select Case Cells(i, 1).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Cumulative").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Worksheets("Cumulative").Range("A1:O39").Copy Destination:= _ Worksheets("Area1").Range("A65536").End(xlUp).Offs et(6).PasteSpecial _ Paste:=xlPasteValues For i = 66 To 34 Step -1 Select Case Cells(i, 2).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Month").Activate Range("A1").Select End Sub |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row if value is not Q
On Feb 7, 1:58 pm, "Bob Phillips" wrote:
You still had a copy Destination in there with Pastespecial. try this Sub FormatArea1() Application.ScreenUpdating = False Sheets("Month").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlFormats Selection.PasteSpecial Paste:=xlPasteValues ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 For i = 39 To 8 Step -1 Select Case Cells(i, 1).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Cumulative").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Worksheets("Cumulative").Range("A1:O39").Copy Worksheets("Area1").Range("A65536").End(xlUp).Offs et(6).PasteSpecial Paste:=xlPasteValues For i = 66 To 34 Step -1 Select Case Cells(i, 2).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Month").Activate Range("A1").Select End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message oups.com... Here it is Bob Sub FormatArea1() Application.ScreenUpdating = False Sheets("Month").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Range("A1").Select Selection.PasteSpecial Paste:=xlFormats Selection.PasteSpecial Paste:=xlPasteValues ActiveWindow.DisplayGridlines = False ActiveWindow.Zoom = 75 For i = 39 To 8 Step -1 Select Case Cells(i, 1).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Cumulative").Activate Cells.Select Selection.Copy Sheets("Area 1").Activate Worksheets("Cumulative").Range("A1:O39").Copy Destination:= _ Worksheets("Area1").Range("A65536").End(xlUp).Offs et(6).PasteSpecial _ Paste:=xlPasteValues For i = 66 To 34 Step -1 Select Case Cells(i, 2).Value Case "City1", "City2", "City3" Case Else Rows(i).Delete Shift:=xlUp End Select Next i Range("A1").Select Sheets("Month").Activate Range("A1").Select End Sub- Hide quoted text - - Show quoted text - Thanks all its working now |
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 |