Macro to loop through a named range, ignoring empty cells

  • Thread starter Thread starter Anika
  • Start date Start date
A

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
 
Anika said:
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
Click to expand...

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
 
Back
Top