score:19
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:
score:0
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
score:0
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
score:0
nextrow = application.worksheetfunction.counta(range("a:a")) + 1
score:0
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
score:0
.find has a lot of options and returns nothing if there are no empty cells found in your range:
with range("f:f")
.find("", .rows(.rows.count), xlformulas, , xlbyrows, xlnext).select
end with
.find starts searching after the first cell in the range by default, so it would normally start in row 2. it will find the first empty cell starting in row 1, if its after argument is the last cell in the range. that's because the search wraps.
score:1
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
score:1
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.
score:2
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
score:2
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
score:7
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
score:9
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
score:17
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.
score:17
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
Source: stackoverflow.com
Related Query
- Select first empty cell in column F starting from row 1. (without using offset )
- Setting a range between a starting cell and the first empty cell in the column using VBA
- Select first Empty Cell from a selected cell (or range), then add a Value (date), then offset and inputext
- Find first column with empty cell in every third row using VBA
- Get row number of first empty cell in column and store that value in other cell
- How to select the entire column starting from row 3 - vba
- Select first empty cell in column AND works for empty column
- Select first empty cell in specific column when switching between worksheets
- How to import data from multiple text files on a folder, starting on line 20, while adding the current file name on the first column using excel VBA
- Find column based on header and remove first 3 characters starting from 2 row
- Copying one-dimensional array of formulas into a listobject column in Excel without auto-filling from first cell
- Count number of row in column B starting from cell B15
- Check if cell from the same row but different column isn't empty
- Find out Excel column character from name of cell in first row
- Select the range from first cell used to last cell used in column
- How to find last row of a column starting from a specific cell
- Copy specific columns from one sheet to another using reference of first cell of column
- Select cell from column with row value
- Paste entire column using as target a single cell which is not in the first row
- How to select a fixed number of adjacent columns starting from N column in excel using VBA
- Using macro to copy multiple ranges from one sheet, and paste them into another sheet in the first empty row
- Locate a cell with a particular value from a given range and find its corresponding value from topmost row and first column
- Select a column from maximum cell to first cell
- Excel Macro Find Last Row In Column B, Select another cell A1, drag formula from A1 to last row in B
- Copying from one sheet pasting to another sheet without using the clipboard, always needs to paste in the first empty row.Getting error 424
- Excel VBA for selecting an entire column starting from a specific cell
- Select first visible cell directly beneath the header of a filtered column
- Excel VBA macro: Locating first empty cell in a column and automatically filling it
- Find a value from a column and quickly return the row number of its cell
- Select data from query without column names
More Query from same tag
- How to remove values from drop-down if value deleted from validation list
- Setting linkedcell of multi-option field
- How can I automatically highlight and extract colored text in MS Word?
- How do I count meeting participants before adding another in Outlook
- excel 2003 vba get cell value in edit mode
- Error while calculating last row of adjucent column
- MS Access Treeview error loading ActiveX control
- master detail subforms in ms access 2010
- Loop to ensure inputbox entry is eight characters long and numeric
- New to Access 2013 - Would like to create a dynamic search form
- Get number of days between two dates using alternate day as a variable
- VBA - Doing Application.VLookup in all sheets and counting when matches
- Excel VBA - sort values and variables
- ppActionRunMacro not working when converted to addin(ppam)
- How do I insert an object from a directory with wildcards?
- How to find duplicates values in a row and then find largest value in another column based on the duplicates?
- Permission error running "Application.CreateItemFromTemplate" method on an EML file
- Loop and set value for coulmns
- User input for date filter
- Userform textbox not populating correctly vba
- Insert Specific Cells of a CSV into MS Access table vba
- How to swap items in a VBA scripting.dictionary
- Cell value for number of rows selected in a range
- Declaring function arguments in excel VBA
- How to merge numerical and alphabet VBA?
- changing font and color size based on cell values
- Excel VBA Transpose contiguous range each empty cell
- MS Access Combobox not taking a value after requery shows value
- Countif and countifs on fairly large dataset - how can I improve?
- Excel VBA code generates emails but crashes Outlook