ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Delete Entire Row if value is not Q (https://www.excelbanter.com/excel-programming/382732-delete-entire-row-if-value-not-q.html)

Sean
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

RichardSchollar
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

Sean
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

Sean
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 -

Bob Phillips
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 -

Sean
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

Mike Fogleman
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

Bob Phillips
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

Sean
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

JMay
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

JMay
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

Sean
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

Bob Phillips
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

Sean
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

Tom Ogilvy
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

Bob Phillips
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

Sean
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


All times are GMT +1. The time now is 08:57 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com