Find first empty cell in row range-VBA

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
228
=Happy+New(Year(2017))

I have three sheets, they have columns A&B, ranges A2:A100, B2:B100. "A" range will always be shorter than B, if that means something. I need vba code to move my selector to First free cell in sheet1 "A"range, another code for "B"range, then another codes for Sheet2...Sheet3.
Thanks in advance kind people!
 
Last edited:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
What is:"move my selector"

So you mean your active cell?

And we only have one active cell per sheet.
So we cannot have a active cell in column "A" and "B"

I do not understand the term "selector"
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
To select the last used cell in Column "A" use this:


Code:
Sub Select_Last_Used_Cell()
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(Lastrow, "A").Select
End Sub

I know this does not exactly answer what you Title question asked for.
Most times people want the last used cell not the first empty cell.
 
Last edited:

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
228
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
            Exit For 'This is missing...
        End If
    Next
End Sub
This code is from StackOverFlow answered by RedLeo

How to restrict this code to ThisWorkbook, because, i'll have more wbooks opened? Reason for this code, i need shortcuts to click and then they put me in first free cell.
Per sheet, i need 2 shortcuts(objects, or activeX), one to scroll "A", second for "B". Thanks in advance kind people!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

I would think its best to tell us in total what your wanting to do. Showing us a script you found and wanting us to understand it and then modify it to meet your needs is not something I like doing.
 

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
228
My English-not good enough.

Let's say i only have sheet1, A&G columns, range A7:A77, G7:G77. Need code, to assign to objects(arrows up&down), When i click up arrow please dear vba move my active cell to first empty cell in range A7:A77, when i click down arrow move me to first free cell in range G7:G77.
Thanks in advance kind people!
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,498
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

My English-not good enough.

Let's say i only have sheet1, A&G columns, range A7:A77, G7:G77. Need code, to assign to objects(arrows up&down), When i click up arrow please dear vba move my active cell to first empty cell in range A7:A77, when i click down arrow move me to first free cell in range G7:G77.
Thanks in advance kind people!

Try this;

Code:
Sub upArrow()


Dim foundBlank As Range
Set foundBlank = Range("A:A").Find(What:="", lookat:=xlWhole)
    foundBlank.Select


End Sub


Sub downArrow()


Dim foundBlank As Range
Set foundBlank = Range("G:G").Find(What:="", lookat:=xlWhole)
    foundBlank.Select


End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,263
Office Version
  1. 2013
Platform
  1. Windows
Try these two scripts:

Code:
Sub First_Empty_Cell_Column_A()
'A7:A77
'For Up Arrow
Dim c As Range
    For Each c In Range("A7:A77")
    If c.Value = "" Then c.Select: Exit Sub
    Next
End Sub

Sub First_Empty_Cell_Column_G()
'G7:G77
'For Down Arrow
Dim c As Range
    For Each c In Range("G7:G77")
    If c.Value = "" Then c.Select: Exit Sub
    Next
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
With a slight modification to SvenDiamonds code....might be quicker than a loop

Code:
Sub upArrow()
Dim foundBlank As Range
Set foundBlank = Range("A7:A77, G7:G77").Find(What:="", lookat:=xlWhole)
    foundBlank.Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,123,269
Messages
5,600,629
Members
414,396
Latest member
rinianjell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top