Fill Blank cells in a range

  • Thread starter Thread starter kevcar40
  • Start date Start date
K

kevcar40

Hi
I have a worksheet that i import data onto
i then count the number of rows and store the result in AM1
the problem is some of the cell are blank
What i want to do is check the range
from BH1 to BH (AM1 value) checking for Blank cells
if any are found put the Word "BLANK" in the cell



Thanks


Kevin
 
Hi Kevin,

Do you really need to fill the empty cells? Whilst you perhaps can't rely on COUNT or COUNTA for this, it occurs to me that what you
probably need is the number of the last data row. If so:
To find the row with the last numeric value in column A, use:
=MATCH(1E+306,A:A,1)
To find the row with the last text value in column A, use:
=MATCH("*",A:A,-1)

Cheers
 
One way:

Dim rBlanks As Range
On Error Resume Next
Set rBlanks = Range("BH1:BH" & _
Range("AM1").Value).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rBlanks Is Nothing Then rBlanks.Value = "BLANK"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top