Find first empty cell in row range-VBA

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
243
Office Version
  1. 2016
Platform
  1. Windows
=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:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,696
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
18,696
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
243
Office Version
  1. 2016
Platform
  1. Windows
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
18,696
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
243
Office Version
  1. 2016
Platform
  1. Windows
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,504
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
 

ljubo_gr

Board Regular
Joined
Dec 6, 2014
Messages
243
Office Version
  1. 2016
Platform
  1. Windows
i have empty cells in a2&a3, I need to look inside 7:77 range.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,696
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
21,279
Office Version
  1. 365
  2. 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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,533
Members
430,295
Latest member
amdis

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