VBA Select next blank "" Cell in specified column after current row. NOT Last full cell+1 to enter data at bottom!!

[Coastline]

There are many other posts with people looking to find the end of the data in a column so they can enter new data in the next empty row - That is Not what I want!

I have many rows of data that I need to step through and enter additional info.

I want to start at the first row of of my sheet select a cell hit the macro button and it takes me to the next row with a blank cell in the D column.

I enter some info and hit the macro button again and it takes me to the next row with a blank in column D.

I realise the code below is completely wrong but I have tried various combinations of range.find.select etc. Just wanted to let you know that I have tried a number of things.

I seem to have hit a wall with how to:

a: define a range using current(activecell) to give me current row to the end of D column.
b: use that range with a find command to find the next "" in the D Column.
c: select the cell containing the blank or I guess a message "no more blanks" or perhaps select the last cell of the column.

Yes I am noob to VBA. Some stuff I get straight away but others I end up down the rabbit hole never to be seen again.

Any help would be gratefully received.

Sub Find_Blank()'
' Find_Blank Macro
'


'
Dim rng As Range
Dim fnd_rng As Range


Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
Set fnd_rge = rng.Find(What:="", _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not fnd_rge Is Nothing Then
                fnd_rge.Select
            Else
                MsgBox "Nothing found"
            End If
            
End Sub

 

[Coastline]

I got it working with the following:

Sub Find_Blank()'
' Find_Blank Macro
'


'
Dim rng As Range
Dim fnd_rng As Range
Dim StartCell As Range


Set StartCell = ActiveCell


Set fnd_rge = Range("G:G").Find(What:="", _
                            After:=StartCell, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
If Not fnd_rge Is Nothing Then
        fnd_rge.Select
Else
        MsgBox "Nothing found"
End If
            
End Sub

 

[leryt]

Here are a few other approaches just to have in case you want to try other options than using the find mechanism.

This one will highlight ALL blank cells in the row using D as a count reference. The First blank you will be able to edit.

Sub Blankfind()
Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Cells.Select
End Sub

This one will search the active worksheet and count all items in Column 4 (column D) and search for "" value. if nothing is located it would then return message. if a Column ALWAYS has a value to represent the row, then change Drowse column number 4 to the indicated column that will always have a value to assure the count range is more accurate.

Sub blankfindt()
Dim rng, srng As Range
Dim Drow As Long
 
Drow = Cells(Rows.Count, 4).End(xlUp).Row + 1
Set srng = Range("$D$1:$D$" & Drow)
 
For Each rng In srng
    If rng.Value = "" Then
        rng.Select
            Exit Sub
    End If
Next rng

 MsgBox "Nothing Found"

End Sub

These are just some other ways you can go about it.

 

[Rick Rothstein]

Give this macro a try...

Sub SelectFirstBlankCellColumnD()
  On Error Resume Next
  Columns("D").SpecialCells(xlBlanks)(1).Select
  If Err.Number Then Cells(Rows.Count, "D").End(xlUp).Offset(1).Select
  On Error GoTo 0
End Sub

 

[DanteAmor]

Give this macro a try...

Sub SelectFirstBlankCellColumnD()
  On Error Resume Next
  Columns("D").SpecialCells(xlBlanks)(1).Select
  If Err.Number Then Cells(Rows.Count, "D").End(xlUp).Offset(1).Select
  On Error GoTo 0
End Sub

The OP does not want the next empty cell after the last data, it wants the first empty cell to be found.

There are many other posts with people looking to find the end of the data in a column so they can enter new data in the next empty row - That is Not what I want!

How about:

Sub NextBlk()
    Range("D" & Evaluate("=MIN(IF(D:D="""",ROW(D:D)))")).Select
End Sub

 

[DanteAmor]

I think it must be like that

Give this macro a try...

Sub SelectFirstBlankCellColumnD()
  On Error Resume Next
  Columns("D").SpecialCells(xlBlanks)(1).Select
  If Err.Number [COLOR=#ff0000]<> 13[/COLOR] Then Cells(Rows.Count, "D").End(xlUp).Offset(1).Select
  On Error GoTo 0
End Sub

 

[My Aswer Is This]

This script will do what you want and not require a button click.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Now when you enter any value in column D you will be taken to the next empty cell in column D

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/11/2019  3:30:56 PM  EDT
If Target.Column = 4 Then
Dim SearchString As String
Dim SearchRange As Range
SearchString = ""
Set SearchRange = Range("D:D").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
SearchRange.Select
End If
End Sub

 

[Coastline]

Thanks for all the suggestions.

The code in the second post does exactly what is required but may not be the most elegant way.

I needed to review the data in each row with a blank in column G. Data may or may not then be entered in Column G. I would then review the next row with a blank in Column G.

The biggest issue I came across was trying to calculate a range that went from ActiveCell to the end of Column G. No matter what I tried I could not get this to work. The intention was always that I would click in Column G then Click the macro button. So the Calculation (assuming ActiveCell was G6) would give me "G6:G" (ie G6 to the end of G column)

Could anyone tell me how to calculate that range for use in the macro. The .Find command has the "After:" attribute so in the end I didn't need to alter the range from "G:G" but it would be nice to know how to do it for the future.

Thanks

 

[DanteAmor]

Thanks for all the suggestions.

The code in the second post does exactly what is required but may not be the most elegant way.

I needed to review the data in each row with a blank in column G. Data may or may not then be entered in Column G. I would then review the next row with a blank in Column G.

The biggest issue I came across was trying to calculate a range that went from ActiveCell to the end of Column G. No matter what I tried I could not get this to work. The intention was always that I would click in Column G then Click the macro button. So the Calculation (assuming ActiveCell was G6) would give me "G6:G" (ie G6 to the end of G column)

Could anyone tell me how to calculate that range for use in the macro. The .Find command has the "After:" attribute so in the end I didn't need to alter the range from "G:G" but it would be nice to know how to do it for the future.

Thanks

Try this

Sub NextBlk()
Range("G" & Evaluate("=MIN(IF(G6:G" & Range("G" & Rows.Count).End(xlUp).Row + 1 & "="""",ROW(G6:G" & Range("G" & Rows.Count).End(xlUp).Row + 1 & ")))")).Select
End Sub

 

[Rick Rothstein]

I needed to review the data in each row with a blank in column G. Data may or may not then be entered in Column G. I would then review the next row with a blank in Column G.

The biggest issue I came across was trying to calculate a range that went from ActiveCell to the end of Column G. No matter what I tried I could not get this to work. The intention was always that I would click in Column G then Click the macro button. So the Calculation (assuming ActiveCell was G6) would give me "G6:G" (ie G6 to the end of G column)

I believe this revision to my previously posted code will do what you want...

[table="width: 500"]
[tr]
	[td]Sub NextBlankCell()
  If ActiveCell.Column = 7 Then
    On Error Resume Next
    Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).SpecialCells(xlBlanks)(1).Select
    If Err.Number Then MsgBox "The next empty cell is below your last data cell!", vbExclamation
    On Error GoTo 0
  Else
    MsgBox "This macro only runs when a cell in Column G is active!", vbExclamation
  End If
End Sub[/td]
[/tr]
[/table]