Find empty cell and input data for other cells

[Rick K]

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

 

[cht13er]

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

 

[Rick Rothstein \(MVP - VB\)]

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