Macro to loop through a named range, ignoring empty cells

[Anika]

Hi

I am new to macro programming in Excel and have encountered a problem.
What I am trying to do is loop through a series of named ranges, skip
any blank cells, and copy and paste any non-blank cells into a
specific place in a word document. This is what I have:

For Each OneCell In Range("RangeName")

If IsEmpty(ActiveCell) Then
ActiveCell.Offset(1, 0).Select

Else: ActiveCell.Copy
wdApp.Activate
wdApp.ActiveDocument.Bookmarks("BookmarkName").Select
wdApp.Selection.PasteAndFormat (wdFormatOrigionalFormatting)
ActiveCell.Offset(1, 0).Select

End If

Next


Then only problem I have is that the code only seems to recognise a
cell as being empty if it is the first cell in the range, otherwise it
just copies and pastes them all!

PS the empty cells do have code in them, but appear blank.

Can anyone help?

Thanks
Anika

 

[mp]

Hi

I am new to macro programming in Excel and have encountered a problem.
What I am trying to do is loop through a series of named ranges, skip
any blank cells, and copy and paste any non-blank cells into a
specific place in a word document. This is what I have:

For Each OneCell In Range("RangeName")

If IsEmpty(ActiveCell) Then
ActiveCell.Offset(1, 0).Select

Else: ActiveCell.Copy
wdApp.Activate
wdApp.ActiveDocument.Bookmarks("BookmarkName").Select
wdApp.Selection.PasteAndFormat (wdFormatOrigionalFormatting)
ActiveCell.Offset(1, 0).Select

End If

Next


Then only problem I have is that the code only seems to recognise a
cell as being empty if it is the first cell in the range, otherwise it
just copies and pastes them all!

PS the empty cells do have code in them, but appear blank.

Can anyone help?

Thanks
Anika

would this work?
Sub test()
Dim OneCell As Variant
For Each OneCell In ThisWorkbook.ActiveSheet.Range("Test").Cells
If OneCell.Value = "" Then
Debug.Print "blank"
Else
Debug.Print OneCell.Value
End If
Next

End Sub

hth
mark