Ex 2002 VBA, Activate First Available Cell in "A"

Duane in Japan


I need to please know how to call the first unused cell in Column A, activate it and put it at the top of my view area.

If I have 7,369 rows of data populated, tomorrow it will be even more, each day I would like to do some work in VBA, which I know how to do, without referencing specific cells. Once complete I need to put my cursor (highlight) in the next unused "A" cell and put it in the left upper corner of my visible sheet. No cell numbers required, maybe the cell past lastused

Thanks in advance for any help.

Shasur

Hi Duane-San

Here you have it

Code Snippet

Sub Get_First_Blank_Cell()

Dim rBlank

On Error Resume Next

Set rBlank = Range("A:A").Cells.SpecialCells(xlCellTypeBlanks)

If Not rBlank Is Nothing Then

ActiveWindow.ScrollRow = rBlank.Row

End If

End Sub

Regards

Sundar

Duane in Japan

Shasur-San,

Almost perfect my friend, I put some of mine in with yours. I had to name it ZzSetup to put it at the end of a listing of active macros that I use everyday.

Sub ZzSetup()
'
' ZzSetup Macro
' Macro recorded 6/1/2007 by Duane
'
Application.CommandBars("Web").Visible = False

Range(ActiveCell, ActiveCell.End(xlDown)).Select

Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Dim rBlank

On Error Resume Next

Set rBlank = Range("A:A").cells.SpecialCells(xlCellTypeBlanks)

If Not rBlank Is Nothing Then

ActiveWindow.ScrollRow = rBlank.Row

End If

Sheets("Zion").Select

End Sub

I first get rid of the (View) Web Toolbar, this thing did not show itself by default some months ago, it must have been a MS update of some sort I have three hyperlink cells in "A" column visible in the top left corner of the sheet when I first open up the workbook, this is due to long time macro necessity from history.

I make the third (last) hyperlink the active cell when I close the workbook everyday, my code then .Select this hyperlink to open it in IE7.

Your code takes over to locate and position the first open cell in the upper left corner of the active sheet. You did this BUT, from the last hyperlink (that is just up out of site, my last active cell) to the very end of "A" column is now highlighted in entirety.

If I code in an Enter or a down arrow to activate the cell of choice, the macro recorder records the number of the first empty cell made active (A8730), so I am please asking how to make just that upper left cell active, not the one above it just out of site.

At the end of my code, I select another sheet where yet another very large macro takes over, thanks for your help.

Duane in Japan

This is mine, yours and one other great helper, I removed one of my lines to stop this thing from highlighting the entire A column of eCell below the target cell. Thanks for your help.

Sub ZzSetup()

Application.CommandBars("Web").Visible = False

Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

Dim colNum As Integer
colNum = 1
Dim eCell As Range
Set eCell = Columns(colNum) _
.SpecialCells(xlCellTypeBlanks).cells(1)

eCell.Select
ActiveWindow.ScrollRow = eCell.Row

Sheets("Zion").Select

End Sub