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" & Rows.Count).End(xlUp).Row + 1
Sheets("SheetName").Range("A" & LastRow).Paste
even
Dim FirstBlankCell as Range
Set FirstBlankCell=Range("A" & 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
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:
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" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
Range("D" & NextFree).Select
NextFree is just a name, you could use sausages if you wanted.
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
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
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 & 1).Column
Row_Counter = 1
Do Until IsEmpty(Cells(Row_Counter, 1))
Row_Counter = Row_Counter + 1
Loop
Cells(Row_Counter, Column_Index).Select
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.
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("A2").Select
NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1
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
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
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& = 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
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
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 & CStr(i)).Value = "" Then
Exit For
End If
Next i
Range(col & CStr(i)).Select
End Sub
Source: Stackoverflow.com