Find and fill the last cell on a specific range on a column

_Fly_

Board Regular
Joined
Jan 6, 2012
Messages
87
Hello my friends.
Here's an example of my sheet

A
B
C
D
E
F
G
1
2
3
Lata
Vidro
Bolsa
4
5
6
7
8
9
10
11
12
Total
=SUM(B4:B11)
13
14

<tbody>
</tbody>

I have a command button, and i want to insert a vb code so every-time i press
that command button he finds the last empty cell on the column B but only between cells B4:B11 and fills that last empty cell with number "5". When he arrives the B11 cell he stops the filling (maybe can also appear a msgbox telling that isn't possible insert more values.
The B12 cell is always filled with the total value (can be a "0" when the range B4:b11 is empty).
Any ideas?
Thank's in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Is this what you want?
Code:
Sub LastEmpty()
Dim c As Range, i As Long, ct As Long
For i = Range("B4:B11").Rows.Count + 3 To 4 Step -1
    If IsEmpty(Cells(i, 2)) Then
        Cells(i, 2).Value = 5
        Exit For
    Else
        ct = ct + 1
    End If
Next i
If ct = 8 Then MsgBox "Insert more values"
End Sub
 
Upvote 0
Hi Joe
Your code work's fine but starts the filling from B11 to B4 it's possible to start from B4 to B11 instead?
Regards

PS: And if i want create another button to do the same to the C4:C11 range and the D4:D11 range?
 
Last edited:
Upvote 0
Hi Joe
Your code work's fine but starts the filling from B11 to B4 it's possible to start from B4 to B11 instead?
Regards

PS: And if i want create another button to do the same to the C4:C11 range and the D4:D11 range?
Your post said fill from last empty cell, can't do that starting at b4. Change the ranges in the code I posted for your other buttons.
 
Upvote 0
Your post said fill from last empty cell, can't do that starting at b4. Change the ranges in the code I posted for your other buttons.
Maybe a bad explanation by me i have really want the first empty cell on the b4:b11 range and I want to start filling from top to bottom begging at B4.
It's really not possible that?
Regards
 
Upvote 0
Maybe a bad explanation by me i have really want the first empty cell on the b4:b11 range and I want to start filling from top to bottom begging at B4.
It's really not possible that?
Regards
Try this:
Code:
Sub FirstEmpty()
Dim c As Range, i As Long, ct As Long
For i = 4 To Range("B4:B11").Rows.Count + Range("B4").Row - 1
    If IsEmpty(Cells(i, 2)) Then
        Cells(i, 2).Value = 5
        Exit For
    Else
        ct = ct + 1
    End If
Next i
If ct = 8 Then MsgBox "Add more values"
End Sub
 
Upvote 0
Maybe:

Code:
Sub Fly_()
Range("B4").End(xlDown)(2).Select
If ActiveCell.Row > 11 Then
    MsgBox "You cannot insert more values"
    Exit Sub
Else
    ActiveCell.Value = 5
End If
End Sub
 
Upvote 0
Try this...

If B4 will always have a value in it...

Code:
Range("B4:B11").Find("*", , xlValues, , xlRows, xlPrevious).Offset(1) = 5

otherwise if B4 could be blank (at the start of filling data for example)...

Code:
If Range("B4").Value = "" Then
  Range("B4").Value = 5
ElseIf Len(Range("B11")) Then
  MsgBox "Nothing left to fill"
Else
  Range("B4:B11").Find("*", , xlValues, , xlRows, xlPrevious).Offset(1) = 5
End If
 
Last edited:
Upvote 0
if i understood well..


Code:
Sub fillIt()
If Evaluate("counta(b4:b11)") < 8 Then
    Range("b4:b11").SpecialCells(xlCellTypeBlanks)(1, 1).Value = 5
Else
    MsgBox "Nothing else to fill."
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top