Move non date/add blank row
 

How can I programmically write, if a number is found in column 1 (A) that is
not a date, move over two columns and drop in column 3 (C).

Then the next step after looping the above is to insert a blank row above
each date found in column 1 (A) for all dates found.

Thanks.

Annette

Move non date/add blank row
 

One way:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myInsertRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
.Offset(0, 2).Value = .Value
.ClearContents
ElseIf IsDate(.Value) Then
If myInsertRng Is Nothing Then
Set myInsertRng = .Cells
Else
Set myInsertRng = Union(.Cells, myInsertRng)
End If
End If
End With
Next myCell
If myInsertRng Is Nothing Then
'do nothing
Else
myInsertRng.EntireRow.Insert
End If
End With

End Sub

But you may want to reconsider inserting that row. I find just making the
rowheight twice as big looks just as nice and it makes subsequent processing a
lot easier.

I'd do it this way:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myInsertRng As Range

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
.Offset(0, 2).Value = .Value
.ClearContents
ElseIf IsDate(.Value) Then
.RowHeight = .RowHeight * 2
End If
End With
Next myCell
End With

End Sub



Annette wrote:

How can I programmically write, if a number is found in column 1 (A) that is
not a date, move over two columns and drop in column 3 (C).

Then the next step after looping the above is to insert a blank row above
each date found in column 1 (A) for all dates found.

Thanks.

Annette

--

Dave Peterson

Move non date/add blank row
 

Dave, the first part is great and is going to be used. I pulled off the
second part because we changed in the middle of the game and decided we
needed to delete all the blank rows left behind that contained no data. I
found a code in the newgroup that will satisfy the 'delete rows' written by
a fellow, Ron de Bruin. All the information I'm dealing with is mainframe
text copied over into the Excel that had to be presented to user in a
workable format. By removing all empty rows, the users can then use filter
and we felt this was a better method. Column C contains a couple rows of
text wherein the associated information found in Col A and B is only the
first line. When the text came over from the mainframe, extra lines were
added sporatically. Sometimes text from what should be column C would shift
and that's the code you wrote. Thanks for the code as it really does the
trick.
"Dave Peterson" wrote in message
...
One way:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myInsertRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
.Offset(0, 2).Value = .Value
.ClearContents
ElseIf IsDate(.Value) Then
If myInsertRng Is Nothing Then
Set myInsertRng = .Cells
Else
Set myInsertRng = Union(.Cells, myInsertRng)
End If
End If
End With
Next myCell
If myInsertRng Is Nothing Then
'do nothing
Else
myInsertRng.EntireRow.Insert
End If
End With

End Sub

But you may want to reconsider inserting that row. I find just making the
rowheight twice as big looks just as nice and it makes subsequent
processing a
lot easier.

I'd do it this way:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim myInsertRng As Range

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
.Offset(0, 2).Value = .Value
.ClearContents
ElseIf IsDate(.Value) Then
.RowHeight = .RowHeight * 2
End If
End With
Next myCell
End With

End Sub



Annette wrote:

How can I programmically write, if a number is found in column 1 (A)
that is
not a date, move over two columns and drop in column 3 (C).

Then the next step after looping the above is to insert a blank row
above
each date found in column 1 (A) for all dates found.

Thanks.

Annette

--

Dave Peterson