hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick

AWESOME!!

cheers thats a great idea! only problem is it seems to keep repeating for
ages?

"Gary''s Student" wrote:

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick

its ok, what kept happeneing is it was deleting all cells that werent yellow
so was going on for ever! made the following adjustment and it works:

Sub movit()
Set s1 = Sheets("Sheet")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
ElseIf Cells(i, "A").Value = " " Then

s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

THankyou again!!

"Patrick Bateman" wrote:

AWESOME!!

cheers thats a great idea! only problem is it seems to keep repeating for
ages?

"Gary''s Student" wrote:

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick

oh and the code i posted before was wrong x sorry x

"Patrick Bateman" wrote:

its ok, what kept happeneing is it was deleting all cells that werent yellow
so was going on for ever! made the following adjustment and it works:

Sub movit()
Set s1 = Sheets("Sheet")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
ElseIf Cells(i, "A").Value = " " Then

s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

THankyou again!!

"Patrick Bateman" wrote:

AWESOME!!

cheers thats a great idea! only problem is it seems to keep repeating for
ages?

"Gary''s Student" wrote:

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick

Well then, let's try a version that may be a bit faster:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub

--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

AWESOME!!

cheers thats a great idea! only problem is it seems to keep repeating for
ages?

"Gary''s Student" wrote:

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick

i have an additional problem that has cropped up.......

the coloured cells are coloured because of conditional formatting and this
causes problems when the cells are coppied and deleted.

any ideas how to get round it? is it possible to remove the conditional
formatting but keep the cells coloured?

"Gary''s Student" wrote:

Well then, let's try a version that may be a bit faster:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub

--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

AWESOME!!

cheers thats a great idea! only problem is it seems to keep repeating for
ages?

"Gary''s Student" wrote:

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick

Not trivial.. See:

http://groups.google.com/group/micro...768bb674d58088
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

i have an additional problem that has cropped up.......

the coloured cells are coloured because of conditional formatting and this
causes problems when the cells are coppied and deleted.

any ideas how to get round it? is it possible to remove the conditional
formatting but keep the cells coloured?

"Gary''s Student" wrote:

Well then, let's try a version that may be a bit faster:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub

--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

AWESOME!!

cheers thats a great idea! only problem is it seems to keep repeating for
ages?

"Gary''s Student" wrote:

For example:

Sub movit()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A:B").Copy s2.Range("A1")
s2.Activate

n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Else
s = "A" & i & ":B" & i
Range(s).Delete Shift:=xlUp
End If
Next
End Sub

First all the data is copied and then the non-yellow data is reomved.
--
Gary''s Student - gsnu200758


"Patrick Bateman" wrote:

hi,

is seem to be going round in circles with this one and wandrered if you can
help?

i have two columns of data say A and B and some of the cells in column a are
coloured yellow
i am trying to write a macro that makes 2 new columns of data on a new sheet
for all the rows that have a cell in column a coloured yellow

any help or ideas would be much appreciated

thankyou

patrick