Find first empty cell in row range-VBA

[ljubo_gr]

=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!

 

[ljubo_gr]

With a slight modification to SvenDiamonds code....might be quicker than a loop

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

How to and where to insert ThisWorkbook and Sheet1.Range...??

 

[Michael M]

Press ALT + F11
Dble click on ThisWorkbook
Paste into space on RH side where cursor is flashing

The code will work on any active worksheet

 

[ljubo_gr]

No, i mean this workbook, i'll have several opened Excel files. And, i need it for sheet2 and sheet3.?

 

[ljubo_gr]

No, i mean this workbook, i'll have several opened Excel files. And, i need it for sheet2 and sheet3.?

So the answer is: Michael M's code inside ThisWorkbook, upArrow is A7:A77range, downArrow code is G7:G77 range, insert arrow objects in Sheet1,2,3 and that's it?

 

[Michael M]

My code does the first empty cell in either range !!
If you want up / down separately use SvenDiamonds code with the ranges set

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

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

 

[ljubo_gr]

Yes, thanks very much. One final question, what if i have different ranges: Sheet1 A7:7 G7:7, Sheet2 A7:77 G7:77, Sheet3 A7:777...
Thanks in advance kind people!

 

[Michael M]

Assuming the ranges require the data to go to the last row/s in column A and G, try using

Sub upArrow()
Dim foundBlank As Range, lr as long
lr = cells(rows.count,"A").end(xlup).row
Set foundBlank = Range("A7:A" & lr).Find(What:="", lookat:=xlWhole)
    foundBlank.Select
End Sub

Sub DownArrow()
Dim foundBlank As Range, lr as long
lr = cells(rows.count,"G").end(xlup).row
Set foundBlank = Range("G7:G" & lr).Find(What:="", lookat:=xlWhole)
    foundBlank.Select
End Sub

 

[svendiamond]

Yes, thanks very much. One final question, what if i have different ranges: Sheet1 A7:7 G7:7, Sheet2 A7:77 G7:77, Sheet3 A7:777...
Thanks in advance kind people!

No, i mean this workbook, i'll have several opened Excel files. And, i need it for sheet2 and sheet3.?

Any time you refer to a range it's going to be in this order:

Workbooks("yourBook").Worksheets("yourSheet").Range("yourRange")

If you just use Range() it's going to use the active sheet. If you use Worksheets().Range() it's going to assume the active workbook. If you want to specify which workbook or sheet you have to use those qualifiers. So to keep it in "ThisWorkbook" you can change it to:

Sub upArrow()Dim foundBlank As Range
Set foundBlank = ThisWorkbook.Sheets("yourSheetName").Range("A7:A77").Find(What:="", lookat:=xlWhole)
    foundBlank.Select
End Sub


Sub DownArrow()
Dim foundBlank As Range
Set foundBlank = ThisWorkbook.Sheets("yourSheetName")Range("G7:G77").Find(What:="", lookat:=xlWhole)
    foundBlank.Select
End Sub

 

[ljubo_gr]

Assuming the ranges require the data to go to the last row/s in column A and G, try using

Sub upArrow()
Dim foundBlank As Range, lr as long
lr = cells(rows.count,"A").end(xlup).row
Set foundBlank = Range("A7:A" & lr).Find(What:="", lookat:=xlWhole)
    foundBlank.Select
End Sub

Sub DownArrow()
Dim foundBlank As Range, lr as long
lr = cells(rows.count,"G").end(xlup).row
Set foundBlank = Range("G7:G" & lr).Find(What:="", lookat:=xlWhole)
    foundBlank.Select
End Sub

THANK YOU ALL! I'll stay with Michael M.
Is this(A7: ) range starts looking from A7 to down? Right?

 

[Michael M]

you could also use an Input to select which column

Sub upArrow()
Dim foundBlank As Range, lr As Long
ans = InputBox("Which Column Do you want to search, A or G ?")
Select Case ans
Case Is = "A"
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set foundBlank = ThisWorkbook.ActiveSheet.Range("A7:A" & lr).Find(What:="", lookat:=xlWhole)
    foundBlank.Select
Case Is = "G"
lr = Cells(Rows.Count, "G").End(xlUp).Row
Set foundBlank = ThisWorkbook.ActiveSheet.Range("G7:G" & lr).Find(What:="", lookat:=xlWhole)
    foundBlank.Select
End Select
End Sub