Select first empty cell in column F starting from row 1 without using offset

Question

This is one query that I am really confused with  Coz I have looked for this so many times but I always find the codes related to finding the last used or first non empty cell  Tried at below codes  diff codes have been separated by the word  even   iRow   Worksheets  Sheet1   Cells Rows Count 1  End XlUp  Row    even  Sub LastCellBeforeBlankInColumn    Range  A1   End xldown  Select  End Sub   even  Find the very last used cell in a Column   Sub LastCellInColumn    Range  A65536   End xlup  Select  End Sub   even  Find the last cell  before a blank in a Row   Sub LastCellBeforeBlankInRow    Range  A1   End xlToRight  Select  End Sub   even  Find the very last used cell in a Row   Sub LastCellInRow    Range  IV1   End xlToLeft  Select  End Sub   even  Worksheets  Sheet1   Range  A1   End xlDown  Row   1   even  LastRow   Range  A   amp  Rows Count  End xlUp  Row   1 Sheets  SheetName   Range  A   amp  LastRow  Paste   even  Dim FirstBlankCell as Range Set FirstBlankCell Range  A   amp  rows Count  end xlup  offset 1 0  FirstBlankCell Activate   Find the last used row in a Column  column A in this example Dim LastRow As Long Dim NextRow As Long With ActiveSheet     LastRow    Cells  Rows Count   F   End xlUp  Row End With NextRow   LastRow   1

User · Answer

I found this thread while trying to carry out a similar task. In the end, I used

Range("F:F").SpecialCells(xlBlanks).Areas(1)(1).Select

Which works fine as long as there is a blank cell in the intersection of the specified range and the used range of the worksheet.

The areas property is not needed to find the absolute first blank in the range, but is useful for finding subsequent non consecutive blanks.

User · Answer

If all you re trying to do is select the first blank cell in a given column  you can give this a try   Range  A1   End xlDown  Offset 1  0  Select   If you re using it relative to a column you ve selected this works   Selection End xlDown  Offset 1  0  Select

User · Answer

I think a Do Until-loop is cleaner  shorter and more appropriate here   Public Sub SelectFirstBlankCell col As String      Dim Column Index as Integer     Dim Row Counter as       Column Index   Range col  amp  1  Column     Row Counter   1      Do Until IsEmpty Cells Row Counter  1           Row Counter   Row Counter   1     Loop      Cells Row Counter  Column Index  Select

User · Answer

If all you re trying to do is select the first blank cell in a given column  you can give this a try   Code   Public Sub SelectFirstBlankCell       Dim sourceCol As Integer  rowCount As Integer  currentRow As Integer     Dim currentRowValue As String      sourceCol   6    column F has a value of 6     rowCount   Cells Rows Count  sourceCol  End xlUp  Row       for every row  find the first blank cell and select it     For currentRow   1 To rowCount         currentRowValue   Cells currentRow  sourceCol  Value         If IsEmpty currentRowValue  Or currentRowValue      Then             Cells currentRow  sourceCol  Select         End If     Next End Sub   Before Selection - first blank cell to select     After Selection

User · Answer

There is another way which ignores all empty cells before a nonempty cell and selects the last empty cell from the end of the first column  Columns should be addressed with their number eg  Col  A    1   With ThisWorkbook Sheets  sheet1             Cells  Cells  Cells Rows Count  1  End xlUp  Row   1  1  select  End With   The next code is exactly as the above but can be understood better   i   ThisWorkbook Sheets  sheet1   Cells Rows Count  j   ThisWorkbook Sheets  sheet1   Cells i  1  End xlUp  Row  ThisWorkbook Sheets  sheet1   Cells j   1  1    textbox1 value

User · Answer

I adapted a bit the code of everyone  made it in a Function  made it faster  array   and added parameters    Public Function FirstBlankCell Optional Sh As Worksheet  Optional SourceCol As Long   1  Optional ByVal StartRow amp    1  Optional ByVal SelectCell As Boolean   False  As Long Dim RowCount As Long  CurrentRow As Long Dim CurrentRowValue As String Dim Data   If Sh Is Nothing Then Set Sh   ActiveSheet  With Sh      rowCount    Cells  Rows Count  SourceCol  End xlUp  Row     Data    Range  Cells 1  SourceCol    Cells rowCount  SourceCol   Value2      For currentRow   StartRow To RowCount         If Data currentRow  SourceCol    vbNullString Then             If SelectCell Then  Cells currentRow  SourceCol  Select              if selection is out of screen  intead of  select   use   application goto reference   cells       scroll   true             FirstBlankCell   currentRow             Exit For         End If     Next  End With   Sh  Erase Data Set Sh   Nothing End Function

User · Answer

This is a very fast and clean way of doing it  It also supports empty columns where as none of the answers above worked for empty columns     Usage  SelectFirstBlankCell  F    Public Sub SelectFirstBlankCell col As String       Dim i As Integer      For i   1 To 10000         If Range col  amp  CStr i   Value      Then             Exit For         End If     Next i      Range col  amp  CStr i   Select End Sub

User · Answer

I just wrote this one-liner to select the first empty cell found in a column based on a selected cell  Only works on first column of selected cells  Modify as necessary Selection End xlDown  Range  quot A2 quot   Select

User · Answer

Code of Sam is good but I think it need some correction   Public Sub SelectFirstBlankCell       Dim sourceCol As Integer  rowCount As Integer  currentRow As Integer     Dim currentRowValue As String      sourceCol   6    column F has a value of 6     rowCount   Cells Rows Count  sourceCol  End xlUp  Row       for every row  find the first blank cell and select it     For currentRow   1 To rowCount         currentRowValue   Cells currentRow  sourceCol  Value         If IsEmpty currentRowValue  Or currentRowValue      Then             Cells currentRow  sourceCol  Select             Exit For  This is missing            End If     Next End Sub   Thanks

User · Answer

NextRow   Application WorksheetFunction CountA Range  A A      1

User · Answer

In case  any one stumbles upon this as I just have     Find First blank cell in a column I m using column D but didn t want to include D1   NextFree   Range  D2 D   amp  Rows Count  Cells SpecialCells xlCellTypeBlanks  Row Range  D   amp  NextFree  Select   NextFree is just a name  you could use sausages if you wanted

User · Answer

If you are looking for a one liner  not including designations and comments  try this  Dim iRow As Long Dim ws As Worksheet Set ws   Worksheets  Name         find first empty cell in column F  coming up from the bottom  and return row number iRow   ws Range  F F   Find What       SearchOrder  xlRows  SearchDirection  xlPrevious  LookIn  xlValues  Row   1

User · Answer

Public Sub SelectFirstBlankCell       Dim sourceCol As Integer  rowCount As Integer  currentRow As Integer     Dim currentRowValue As String      sourceCol   6    column F has a value of 6     rowCount   Cells Rows Count  sourceCol  End xlUp  Row       for every row  find the first blank cell and select it     For currentRow   1 To rowCount         currentRowValue   Cells currentRow  sourceCol  Value         If IsEmpty currentRowValue  Or currentRowValue      Then             Cells currentRow  sourceCol  Select         End If     Next End Sub   If any column contains more than one empty cell continuously then this code will not work properly

[vba] Select first empty cell in column F starting from row 1. (without using offset )

Examples related to vba

Examples related to excel