• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

Select third blank cell in row

How do I tell this to select the third blank cell in row 4?  Then I increment the (1) it moves down another cell.  

Rows("4").SpecialCells(xlBlanks).Cells(1).Activate
0
RWayneH
Asked:
RWayneH
  • 4
  • 2
1 Solution
 
NorieVBA ExpertCommented:
Are the cells in row 4 filled with non-contiguous values?
0
 
RWayneHAuthor Commented:
yes..  2 nonblank, 2 blank, 3 non than another blank.  Also it will vary based on a download.  It could even by the first three of the row are blank... and it should pick C4.

Hope this helps. -R-
0
 
Rob HensonFinance AnalystCommented:
You can include this into a Do Loop:

Count = 0
Rows("4").Select
Do 
  For each Cell in Selection
  If cell.value = "" then count = count +1
  Col = Cell.Column
Loop Until Count = 4
  Cells(4, Col).Select

Open in new window

Thanks
Rob H

Edit:  line 7 should be: Loop Until Count = 3
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RWayneHAuthor Commented:
Compile Error on Ln7  "Loop without Do"  Please advise and thanks. -R-
0
 
RWayneHAuthor Commented:
Here is what I came up with... is there a way to condense this down?  Your code did not work. -R-

Sub TestFindThirdBlankCell()

   Range("C4").Select
   ActiveCell.FormulaR1C1 = " "
   
   Rows("4").Select
    Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(0, 1).Select   '(1, 0) = move down one.  0, 1 = move one right
    Loop  'ends on the blank
   ActiveCell.FormulaR1C1 = " "
'Do it again
   Rows("4").Select
    Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(0, 1).Select   '(1, 0) = move down one.  0, 1 = move one right
    Loop  'ends on the blank
   ActiveCell.FormulaR1C1 = "Total Price"
   ActiveCell.Offset(0, 1).Select  'move one right
   ActiveCell.FormulaR1C1 = "Cost Over Base"

End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
I have now spotted the error but surprised it was reported in the way you mention.

Code should be:

Count = 0
Rows("4").Select
Do 
  For each Cell in Selection
  If cell.value = "" then count = count +1
  Col = Cell.Column
  Next Cell
Loop Until Count = 4
  Cells(4, Col).Select

Open in new window


Note addition of "Next Cell" in Line 7
Apologies,
Rob H
0
 
RWayneHAuthor Commented:
Thanks worked awesome.. EXCELent!  -R-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now