CUT not COPY but errors

L

L. Howard

What is making this Cut snippet error?

I've tried:

cG.Row.EntireRow.Cut
ActiveCell.EntireRow.Cut

I had it in my mind Cut or Copy could be used interchangeably.
My cheat sheet and Google are woefully short on examples.

Thanks.
Howard

With ActiveWorkbook.Worksheets("Sheet1")
Set Grng = Range("G1:G" & .Range("G" & Rows.Count).End(xlUp).Row)
For Each cG In Grng
If cG = "X" Then
cG.EntireRow.Cut
Sheets("All Other").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial
End If
Next
End With
 
C

Claus Busch

Hi Howard,

Am Wed, 22 Jan 2014 19:02:28 -0800 (PST) schrieb L. Howard:
What is making this Cut snippet error?
Click to expand...

try:
With ActiveWorkbook.Worksheets("Sheet1")
Set Grng = .Range("G1:G" & .Range("G" & Rows.Count).End(xlUp).Row)
For Each cG In Grng
If cG = "X" Then
cG.EntireRow.Cut _
Sheets("All Other").Range("A" & Rows.Count).End(xlUp)(2)
End If
Next
End With


Regards
Claus B.
 
L

L. Howard

Hi Howard,



Am Wed, 22 Jan 2014 19:02:28 -0800 (PST) schrieb L. Howard:






try:

With ActiveWorkbook.Worksheets("Sheet1")

Set Grng = .Range("G1:G" & .Range("G" & Rows.Count).End(xlUp).Row)

For Each cG In Grng

If cG = "X" Then

cG.EntireRow.Cut _

Sheets("All Other").Range("A" & Rows.Count).End(xlUp)(2)

End If

Next

End With





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Works nicely. I could have sworn that I tried that all on one line as you point out. Not sure now.

Thanks Claus.

Regards,
Howard
 
C

Claus Busch

Hi Howard,

Am Wed, 22 Jan 2014 23:54:25 -0800 (PST) schrieb L. Howard:
Works nicely. I could have sworn that I tried that all on one line as you point out. Not sure now.
Click to expand...

you forgot the dot in front of Range in that line:
Set Grng = Range("G1:G" & .Range("G" & Rows.Count).End(xlUp).Row)

If you started the macro from another sheet which had no data in G the
macro failed.


Regards
Claus B.
 
L

L. Howard

Hi Howard,



Am Wed, 22 Jan 2014 23:54:25 -0800 (PST) schrieb L. Howard:






you forgot the dot in front of Range in that line:

Set Grng = Range("G1:G" & .Range("G" & Rows.Count).End(xlUp).Row)



If you started the macro from another sheet which had no data in G the

macro failed.





Regards

Claus B.
Click to expand...

Sorry, you lost me. Where was the . (dot) missing?

Also the macro is in the sheet module of Sheet1. And column G has no blanks until this snippet runs correctly, where it removes the X and other row data.

Howard
 
C

Claus Busch

Hi Howard,

Am Thu, 23 Jan 2014 02:19:41 -0800 (PST) schrieb L. Howard:
Sorry, you lost me. Where was the . (dot) missing?
Click to expand...

that is your line:
Set Grng = Range("G1:G" & .Range("G" & Rows.Count).End(xlUp).Row)
^^^ ^^^^
Set Grng = .Range("G1:G" & .Range("G" & .Rows.Count).End(xlUp).Row)


Regards
Claus B.
 
L

L. Howard

that is your line:
Set Grng = Range("G1:G" & .Range("G" & Rows.Count).End(xlUp).Row)

^^^ ^^^^

Set Grng = .Range("G1:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
Click to expand...
Regards

Claus B.

--
Click to expand...
Okay, I see what you mean on the dot.

Here is the whole code and the similar sections of code DO NOT have the dot there and work fine. And works fine WITH dots in those locations. (I tried it both ways, all with dots and all with no dots and it works)

But I could expect the code to fail as you said here:

<If you started the macro from another sheet which had no data in G the
macro failed.>

Is that true?

Howard

Option Explicit

Sub MyColDEFG()
Dim cG As Range
Dim Grng As Range

Dim cD As Range
Dim Drng As Range

Dim cE As Range
Dim Erng As Range

Dim cF As Range
Dim Frng As Range

Application.ScreenUpdating = False

With ActiveWorkbook.Worksheets("Sheet1")

Set Grng = .Range("G1:G" & .Range("G" & Rows.Count).End(xlUp).Row)
For Each cG In Grng
If cG = "X" Then
cG.EntireRow.Cut Sheets("All Other").Range("A" & Rows.Count).End(xlUp)(2)
End If
Next

Set Drng = Range("D1:D" & .Range("D" & Rows.Count).End(xlUp).Row)
For Each cD In Drng
If cD <> "" And cD.Offset(, 2) = "" Then
cD.Offset(, -3).Resize(1, 7).Copy
Sheets("Tab 1").Range("D" & Rows.Count).End(xlUp)(2).PasteSpecial
End If
Next

Set Erng = Range("E1:E" & .Range("E" & Rows.Count).End(xlUp).Row)
For Each cE In Erng
If cE <> "" And cE.Offset(, 1) = "" Then
cE.Offset(, -4).Resize(1, 7).Copy
Sheets("Tab 2").Range("E" & Rows.Count).End(xlUp)(2).PasteSpecial
End If
Next

Set Frng = Range("F1:F" & .Range("F" & Rows.Count).End(xlUp).Row)
For Each cF In Frng
If cF = "W" Then
cF.Copy
Sheets("Tab 3").Range("F" & Rows.Count).End(xlUp)(2).PasteSpecial
ElseIf cF = "O" Then
cF.Copy
Sheets("Tab 1").Range("L" & Rows.Count).End(xlUp)(2).PasteSpecial
Sheets("Tab 2").Range("L" & Rows.Count).End(xlUp)(2).PasteSpecial
End If
Next

End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 
C

Claus Busch

Hi Howard,

Am Thu, 23 Jan 2014 04:37:21 -0800 (PST) schrieb L. Howard:
Here is the whole code and the similar sections of code DO NOT have the dot there and work fine. And works fine WITH dots in those locations. (I tried it both ways, all with dots and all with no dots and it works)
Click to expand...

if you use a With statement the dot will refer explicit to the sheet in
this statement.
If you set a range without refering to a sheet VBA normally refers to
the active sheet.
ActiveWorkbook could be another workbook than the workbook with the
code. The workbook with the code in it is ThisWorkbook.
It is no worksheet event code. So the code should be in a standard
module.

If your ranges all have the same number of rows you only have to
calculate the last cell once. The other ranges can set using offset.
If you refer to the rows instead of copy and paste the code is a bit
faster:

Sub MyColDEFG()
Dim cG As Range
Dim Grng As Range

Dim cD As Range
Dim Drng As Range

Dim cE As Range
Dim Erng As Range

Dim cF As Range
Dim Frng As Range
Dim arrOut As Variant

Application.ScreenUpdating = False

With ActiveWorkbook.Worksheets("Sheet1")

Set Grng = .Range("G1:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
For Each cG In Grng
If cG = "X" Then
cG.EntireRow.Cut Sheets("All Other") _
.Range("A" & Rows.Count).End(xlUp)(2)
End If
Next

Set Drng = Grng.Offset(, -3)
For Each cD In Drng
If cD <> "" And cD.Offset(, 2) = "" Then
arrOut = cD.Offset(, -3).Resize(1, 7)
Sheets("Tab 1").Range("D" & Rows.Count) _
.End(xlUp)(2).Resize(columnsize:=7) = arrOut
End If
Next

Set Erng = Grng.Offset(, -2)
For Each cE In Erng
If cE <> "" And cE.Offset(, 1) = "" Then
arrOut = cE.Offset(, -4).Resize(1, 7)
Sheets("Tab 2").Range("E" & Rows.Count) _
.End(xlUp)(2).Resize(columnsize:=7) = arrOut
End If
Next

Set Frng = Grng.Offset(, -1)
For Each cF In Frng
If cF = "W" Then
Sheets("Tab 3").Range("F" & Rows.Count).End(xlUp)(2) = cF
ElseIf cF = "O" Then
Sheets("Tab 1").Range("L" & Rows.Count).End(xlUp)(2) = cF
Sheets("Tab 2").Range("L" & Rows.Count).End(xlUp)(2) = cF
End If
Next

End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
L

L. Howard

Hi Howard,



Am Thu, 23 Jan 2014 04:37:21 -0800 (PST) schrieb L. Howard:






if you use a With statement the dot will refer explicit to the sheet in

this statement.

If you set a range without refering to a sheet VBA normally refers to

the active sheet.

ActiveWorkbook could be another workbook than the workbook with the

code. The workbook with the code in it is ThisWorkbook.

It is no worksheet event code. So the code should be in a standard

module.



If your ranges all have the same number of rows you only have to

calculate the last cell once. The other ranges can set using offset.

If you refer to the rows instead of copy and paste the code is a bit

faster:



Sub MyColDEFG()

Dim cG As Range

Dim Grng As Range



Dim cD As Range

Dim Drng As Range



Dim cE As Range

Dim Erng As Range



Dim cF As Range

Dim Frng As Range

Dim arrOut As Variant



Application.ScreenUpdating = False



With ActiveWorkbook.Worksheets("Sheet1")



Set Grng = .Range("G1:G" & .Range("G" & .Rows.Count).End(xlUp).Row)

For Each cG In Grng

If cG = "X" Then

cG.EntireRow.Cut Sheets("All Other") _

.Range("A" & Rows.Count).End(xlUp)(2)

End If

Next



Set Drng = Grng.Offset(, -3)

For Each cD In Drng

If cD <> "" And cD.Offset(, 2) = "" Then

arrOut = cD.Offset(, -3).Resize(1, 7)

Sheets("Tab 1").Range("D" & Rows.Count) _

.End(xlUp)(2).Resize(columnsize:=7) = arrOut

End If

Next



Set Erng = Grng.Offset(, -2)

For Each cE In Erng

If cE <> "" And cE.Offset(, 1) = "" Then

arrOut = cE.Offset(, -4).Resize(1, 7)

Sheets("Tab 2").Range("E" & Rows.Count) _

.End(xlUp)(2).Resize(columnsize:=7) = arrOut

End If

Next



Set Frng = Grng.Offset(, -1)

For Each cF In Frng

If cF = "W" Then

Sheets("Tab 3").Range("F" & Rows.Count).End(xlUp)(2) = cF

ElseIf cF = "O" Then

Sheets("Tab 1").Range("L" & Rows.Count).End(xlUp)(2) = cF

Sheets("Tab 2").Range("L" & Rows.Count).End(xlUp)(2) = cF

End If

Next



End With

Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...



Thanks Claus for the explanation and the upgraded code.

That should make an excellent example to refer to in the future.

Regards,
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

A simple if <> nn then Msgbox "No Match" 5
Code to Resize a variable number of rows including target 7
Open four Wbooks, copy four columns from each to Master Wbook 16
Select Case Code does not run... 10
Object variable or with Block variable not set 9
Copy columns and paste with blank column between 5
Return last row no. of column with blanks at top 7
Resize a row but copy but skip columns I don't want to copy 6

Top