Blank cell in row 1

[simoncpage]

I need a function that returns the first empty cell of a specific row. I have tried using Instr() to locate a cell with "" in but the cells in that row contain nulls anyway so that doesn't work. I have a function that find the last cell but im not sure how to alter it for a specific row?

any Help?? would be great

-------------------------------------------
Function findlastreviewscol(Column As String)

Dim maxrow As Integer, maxcolumn As Integer

maxrow = 1
maxcolumn = 1

For Each cell In Intersect(ActiveSheet.Range("" & Column & ":" & Column & "&quot, ActiveSheet.UsedRange)
If cell.Text <> &quot;&quot; Then
If cell.Row > maxrow Then
maxrow = cell.Row
End If
If cell.Column > maxcolumn Then
maxcolumn = cell.Column
End If
End If
Next cell

findlastreviewscol = maxcolumn

End Function
----------------------------------

thanks

 

[DaleWatson123321]

Simon,

I created the following, and it seems to work fine...

Sub Goto_FirstBlankCell_CurrentRow()
Range(&quot;A&quot; & ActiveCell.Row).Select
If ActiveCell.Value = &quot;&quot; Then Exit Sub
If ActiveCell.Offset(0, 1).Value = &quot;&quot; Then
ActiveCell.Offset(0, 1).Select
Exit Sub
Else
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
End If
End Sub

Hope this helps. ...please advise as to whether it &quot;fits&quot;.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[simoncpage]

nope thats not what i actually want...perhaps I wasnt clear. I want a function that returns a value say &quot;10&quot; which indicated that a specific row has 10 non-empty cells?

 

[SkipVought]

Her's another way...

Sub SelectFirstEmptyCell()
    With Cells(ActiveCell.Row, 1)
        If IsEmpty(.Value) Then
            .Select
        Else
            .End(xlToRight).Offset(0, 1).Select
        End If
    End With
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[simoncpage]

Nope skip still not quite what I want as I said I want it to return a value not goto. Look at my example above it returns basically what I am looking for but instead of a whole sheet I want just a single row!

 

[SkipVought]

Well Simon Page, if you were to take the Selection, you could find the Row and Column.

You said, &quot;...returns the first empty cell of a specific row.&quot; Nothing there about any of the Properties of the first empty cell. BOTH of our functions give you the FIRST EMPTY CELL in a row.

Maybe you should spend a little more time working on the wording of your questions, so as NOT to waste the time of good contributors like Dale Watson and others. :-( Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Simon,

I'm &quot;back again&quot; - with an update...

Note: This routine will require:

1) assign Range Name &quot;current_row&quot; to a row on &quot;Sheet2&quot; - the ENTIRE row. (I've used row 1)

2) enter this formula in a cell on Sheet2 (I've used cell A2) ... =COUNTA(current_row)

3) assign Range Name &quot;filled_cells&quot; to cell A2.

Sub Count_NonEmptyCells_CurrentRow()
Application.ScreenUpdating = False
ActiveCell.EntireRow.Copy
Worksheets(&quot;Sheet2&quot.Select
Range(&quot;current_row&quot.Select
ActiveSheet.Paste
Worksheets(&quot;Sheet1&quot.Select
CutCopyPaste = False
numfilled = Range(&quot;filled_cells&quot.Value
If numfilled = 1 Then
MsgBox &quot;There is &quot; & numfilled & &quot; Non-Empty cell on this row.&quot;
ElseIf numfilled > 1 Then
MsgBox &quot;There are &quot; & numfilled & &quot; Non-Empty cells on this row.&quot;
Else
MsgBox &quot;There are NO Non-Empty cells on this row.&quot;
End If
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

Hope this is what you wanted. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[simoncpage]

Sorry Skip

Your right I didn't write what I wanted clearly I wrote it too quickly and relied on my code to much to explain what I wanted, I'll try hard in the future.

Thanks for your reply!

Simon

 

[SkipVought]

Function CountEmptyCells(rng As Range) As Integer
    Dim isect, iCount
    Set isect = Intersect(rng.EntireRow, ActiveSheet.UsedRange)
    iCount = 0
    For Each cell In isect
        cell.Select
        If IsEmpty(cell.Value) Then iCount = iCount + 1
    Next
    CountEmptyCells = iCount
End Function

Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Ya know sumthin, simpn page?

A guy like Dale Watson has posted 9 questions and 208 Stars in appreciation of other contributors.

You, on the other hand, have posted 26 questions and have imparted a mere 4 Stars.
Hmmmmmmm? Skip,
metzgsk@voughtaircraft.com

 

[simoncpage]

Skip, this site shouldnt be about who has the most stars - it should be about sharing and maybe being friendly? - obviuosly you don't thats your opinion and I can respect that. I havent come here to piss anyone off, and I am sorry if I have upset you.

Simon