Hi,

I'm having no problems with selecting and manipulating single cell
with variables but don't understand how to convert this to working wit
Range. For example, in a macro I''ll find a row with the text "Starts:
in column A. Working on any single cell (or complete row) reference
from that start point isn't a problem, but if I want to work with mor
than one cell, I do it one cell at a time because I can't figure ou
how to select more than one using a Range with variables.

I currently use this to delete some cells (columns I to M) and the
move columns A to I to columns J to R.

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = cLastRow To 1 Step -1
If InStr(1, Cells(r, "A"), "Starts:") 0 Then
Cells(r, "I").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Cut Destination:=Cells(r, "R")
Cells(r, "H").Select
Selection.Cut Destination:=Cells(r, "Q")
Cells(r, "G").Select
Selection.Cut Destination:=Cells(r, "P")
Cells(r, "F").Select
Selection.Cut Destination:=Cells(r, "O")
Cells(r, "E").Select
Selection.Cut Destination:=Cells(r, "N")
Cells(r, "D").Select
Selection.Cut Destination:=Cells(r, "M")
Cells(r, "C").Select
Selection.Cut Destination:=Cells(r, "L")
Cells(r, "B").Select
Selection.Cut Destination:=Cells(r, "K")
Cells(r, "A").Select
Selection.Cut Destination:=Cells(r, "J")
End If
Next r

Very messy but it works.

I then use a similar procedure later to move the same range back t
column A (need to get them out of the way for some text to colum
stuff). I know it can be simplified with a range, but how do I do it

--
Mark
-----------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411
View this thread: http://www.excelforum.com/showthread.php?threadid=57382

Never mind, figured it out (with a little help from the recorder) using
offsets from the active cell. So now I have this instead:

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = cLastRow To 1 Step -1
If InStr(1, Cells(r, "A"), "Starts:") 0 Then
Cells(r, "A").Select
ActiveCell.Offset(0, 8).Range("A1:E1").Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, -8).Range("A1:I1").Select
Selection.Cut Destination:=ActiveCell.Offset(0,
9).Range("A1:I1")
End If
Next r

Much better I think. Understand a little more about offsets now.

Cheers

Mark K Wrote:
Hi,

I'm having no problems with selecting and manipulating single cells
with variables but don't understand how to convert this to working with
Range. For example, in a macro I''ll find a row with the text "Starts:"
in column A. Working on any single cell (or complete row) referenced
from that start point isn't a problem, but if I want to work with more
than one cell, I do it one cell at a time because I can't figure out
how to select more than one using a Range with variables.

I currently use this to delete some cells (columns I to M) and then
move columns A to I to columns J to R.

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For r = cLastRow To 1 Step -1
If InStr(1, Cells(r, "A"), "Starts:") 0 Then
Cells(r, "I").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Cut Destination:=Cells(r, "R")
Cells(r, "H").Select
Selection.Cut Destination:=Cells(r, "Q")
Cells(r, "G").Select
Selection.Cut Destination:=Cells(r, "P")
Cells(r, "F").Select
Selection.Cut Destination:=Cells(r, "O")
Cells(r, "E").Select
Selection.Cut Destination:=Cells(r, "N")
Cells(r, "D").Select
Selection.Cut Destination:=Cells(r, "M")
Cells(r, "C").Select
Selection.Cut Destination:=Cells(r, "L")
Cells(r, "B").Select
Selection.Cut Destination:=Cells(r, "K")
Cells(r, "A").Select
Selection.Cut Destination:=Cells(r, "J")
End If
Next r

Very messy but it works.

I then use a similar procedure later to move the same range back to
column A (need to get them out of the way for some text to column
stuff). I know it can be simplified with a range, but how do I do it?


--
Mark K
------------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117
View this thread: http://www.excelforum.com/showthread...hreadid=573825