Hi,
I want to move (cut paste?) cell content from columns D:F to the left by
one position (offset?). When cell C is empty. This test I am trying is setup
for
only 4 rows d2:d5 (hear in rown 1), but the real work will have 2300 rows
involved. This is
a situation where data coming is to us is not formated correctly (i.e.)
city,state,zip
which should be situated in cells C,D & E is on some rows located in D,E & F

Sub OffsetRange()
Dim r As Range
Dim myVar
Dim myCell As Range
Set myCell = Range("C2:c5")
Dim c As Range
Set c = Range("D:F")
For Each r In Range("C2:C5" & Cells(Rows.Count, "c").End(xlUp).Row)
myVar = IsEmpty(r.Value)
If myVar = True Then
?????? I'm lost here

End If
Next
End Sub

Any help appreciated
Rick

Hey, Rick. I'd do it like this:

Public Sub OffsetRange()
cells(2,1).select '"A2"

do until activecell = "" 'til last row of the column A
if activecell.offset(0,2)="" Then 'C is blank
activecell.offset(0,2)=activecell.offset(0,3)
activecell.offset(0,3)=activecell.offset(0,4)
activecell.offset(0,4)=activecell.offset(0,5)
end if
End Sub

You didn't say if there was anything in columns G, H, etc.....

Chris

On Mar 11, 9:22*am, Rick K wrote:
Hi,
* I want to move (cut paste?) cell content from columns D:F to the left by
one position (offset?). When cell C is empty. This test I am trying is setup
for
only 4 rows d2:d5 (hear in rown 1), but the real work will have 2300 rows
involved. This is
a situation where data coming is to us is not formated correctly (i.e.)
city,state,zip
which should be situated in cells C,D & E is on some rows located in D,E & F

Sub OffsetRange()
Dim r As Range
Dim myVar
Dim myCell As Range
* * Set myCell = Range("C2:c5")
Dim c As Range
* * Set c = Range("D:F")
* * For Each r In Range("C2:C5" & Cells(Rows.Count, "c").End(xlUp).Row)
* * *myVar = IsEmpty(r.Value)
* * * * If myVar = True Then
* * * * ?????? I'm lost here

* * * * End If
* * Next
End Sub

Any help appreciated
Rick

Give this macro a try...

Sub MoveBadRanges()
Dim R As Range
With Worksheets("Sheet2")
For Each R In .Range("C1:C" & .UsedRange.Rows.Count).Cells. _
SpecialCells(xlCellTypeBlanks)
R.Offset(0, 1).Resize(1, 3).Copy R
R.Offset(0, 3).Clear
Next
End With
End Sub

Rick


"Rick K" wrote in message
...
Hi,
I want to move (cut paste?) cell content from columns D:F to the left by
one position (offset?). When cell C is empty. This test I am trying is
setup
for
only 4 rows d2:d5 (hear in rown 1), but the real work will have 2300 rows
involved. This is
a situation where data coming is to us is not formated correctly (i.e.)
city,state,zip
which should be situated in cells C,D & E is on some rows located in D,E &
F

Sub OffsetRange()
Dim r As Range
Dim myVar
Dim myCell As Range
Set myCell = Range("C2:c5")
Dim c As Range
Set c = Range("D:F")
For Each r In Range("C2:C5" & Cells(Rows.Count, "c").End(xlUp).Row)
myVar = IsEmpty(r.Value)
If myVar = True Then
?????? I'm lost here

End If
Next
End Sub

Any help appreciated
Rick