Code to loop down a column and find the next empty cell 1

[protector]

Hi everyone

This maybe really simple but I tried most of the code I could find and can't get it to work. What I would like to do is on a button click > Open worksheet > Search down column A till the first empty cell > Enter details in cells along this row id.

I know how to open the worksheet and I'm pretty sure(?) I can figure out how to get the data into the specific cells in the row, I just can't seem to find the next empty cell in column A.

All help greatly appreciated as always

Thanks and Regards
Craig

 

[ratman]

Consider this:

Private Sub CommandButton1_Click()
yourvariable = TextBox1.Value
If IsEmpty(Range("Sheet1!A2&quot.Value) Then
lastrow = 1
Else
lastrow = [Sheet1!A1].End(xlDown).Row
End If
Range("A" & lastrow + 1).Value = yourvariable
End Sub Thank you,
Dave Rattigan

 

[bryanbayfield]

sub blank_A()
Dim rgFoundCell As Range, n As Integer
Set rgFoundCell = Sheets("Sheet1&quot.UsedRange.Columns("A&quot.Find(what:="&quot
n = Sheets("Sheet1&quot.UsedRange.Columns.Count
Do Until rgFoundCell Is Nothing
rgFoundCell.Range(Cells(1, 1), Cells(1, n)).FormulaArray = "Blank A" ' change as nec.
Set rgFoundCell = Sheets("Sheet1&quot.UsedRange.Columns("A&quot.FindNext
Loop
End Sub

 

[bryanbayfield]

It's late and I may have misinterpreted what you wanted.

Hmmm ... time for bed!

 

[xlbo]

I know you've already had a coupla answers but I'm bored so
In the command button click sub

lRow = sheets("DataEntrySheet&quot.range("A65536&quot.end(xlup).row
With sheets("DataEntrySheet&quot
.range("A" & lRow).value = "1st Bit of data to enter"
.range("B" & lRow).value = "2nd bit of data to enter"
etc etc
End With

This will allow you to enter data into a row on a sheet without actually activating or selecting the sheet
oh yeh - make sure that the TakeFocusOnCLick property of the command button is set to false

HTH
~Geoff~

 

[bryanbayfield]

Geoff, that will only work for contiguous ranges of data.

 

[xlbo]

Bryan - actually, it looks from the bottom up so it doesn't matter whether there are breaks in the column (ie non contiguous) or not. However, Craig specified "Search down column A till the FIRST EMPTY CELL", indicating either that the data range is, in fact, contiguous

Gotta say, also, that "UsedRange" can be unreliable UNTIL you have done a column and row count on it - this seems to reset it and it is then usable. If I am going to use it, I always have
r = usedrange.rows.count
c = usedrange.columns.count
BEFORE I reference the Usedrange in code

Hope you had a good sleep ;-)
HTH
~Geoff~

 

[ratman]

Hi folks,

If protector(Craig) is looking to:

"What I would like to do is on a button click > Open worksheet > Search down column A till the first empty cell > Enter details in cells along this row id."

My post would do that correct?
Just wondering if I'm not interpreting his request correctly. More of a sanity check for myself than anything.

Have a good day. Thank you,
Dave Rattigan

 

[bryanbayfield]

Personally, I think we've all gone over the top.

Click on the Open Icon > Open file (store a shortcut for it in the Favourites subfolder if necessary beforehand) > Go to required sheet, range "A1" > Press Ctrl + down cursor key.

It would barely take any longer than the macro.

Geoff - maybe I didn't get enough sleep last night - I'm not understanding you. First empty cell is in row eg 7, data finishes at row 50. Your code would find row 50, wouldn't it? 7 ^= 50 => Problem. I think I need a sanity check now as well.

Dave - your code would work, yeah.

 

[bryanbayfield]

Thanks for the tip about UsedRange by the way Geoff.

 

[xlbo]

Ratman - yup, you're code would work perfectly well, as I said, I was bored so I thought I'd post a differenht approach

Bryan - no worries about the tip - it only matters when data is cleared from a cell rather than deleted. If that cell is below or to the right of your new used range, UsedRange doesn't compensate until a save or until it's made to re-check. Also, you're perfectly right about when there is gaps in data but from the wording of the question, I don't reckon there are. If so, then yes, xldown will work fine. Consider this tho

Summary lines of data in 1st 10 rows
2 row gap
DataRange for a coupla hundred rows

If you use Range("A13&quot.end(xldown).row, that'll get you the last row of data BUT, if you change the amount of summary at the top, you then have to change your code as well - that's why I tend to use 65536.en(xlup) - doesn't matter what's above it, it'll always get the last cell of data

HTH
~Geoff~

 

[zcjl]

Try this:

Private Sub CommandButton1_Click()
Dim RowLast as long
yourvariable = TextBox1.Value
RowLast=sheets("Sheet1&quot.range("A1&quot.End(xlDown).row + 1
If RowLast > (2^16) then
RowLast = 1
If len(Range("A1&quot)>0 then RowLast = 2
end If
Range("A" & RowLast) = yourvariable
End Sub

 

[bryanbayfield]

Geoff, at the risk of turning this thread into a "Onedin Line" saga, I have a tip for you now.

UsedRange - it's easily reset with the line:

ActiveSheet.UsedRange

and that's it!

I'd much rather "use" UsedRange since there are no literal values to take account of. It's a moot stylistic point, I guess.

 

[xlbo]

Cheers Bryan - have to say, I stopped using it a while back 'cos I found out it didn't work 100% and found other ways of doing the same thing. When I found out how to correct it, I'd already got used to coding using other methods so half the time I just forget it's actually there. Quick question tho, from your earlier posting, you have the line:
Set rgFoundCell = Sheets("Sheet1&quot.UsedRange.Columns("A&quot.Find(what:="&quot
What does the UsedRange.Columns("A&quot do - surely, you could just use Columns("A&quot...???
HTH
~Geoff~

 

[bryanbayfield]

It's a way of restricting the number of rows looked at.

If the UsedRange wasn't in there it would go beyond the data table and start processing blank cells that you wouldn't want processed.

 

[xlbo]

Think this might get a bit cyclical but I was of the impression that FIND inherently uses the UsedRange. Test this

Enter a bunch of junk in column A down to row 200

clear 1 cell

Highlight column A

CTRL+F

Search for nothing ("&quot

you will get 2 results only - the cell you cleared and the 1st cell after your data range. Autofilter also uses this trick. I would say that you probably don't need the usedrange bit as part of FIND, although being as I posted that I do stuff just 'cos it's the way I've got used to writing, I ain't expecting you to stop using it...
;-)

anyway, it's probably about time this little thread went to bed - been useful tho ;-) HTH
~Geoff~