Check box select next blank cell.

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
I am trying to get a check box to check a range for the first available blank cell. I then want to call a macro.


Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Range("A10:z19").Select 'select a1
Selection.End(xlToRight).Select 'go to last cell to right
ActiveCell.Offset(0, 1).Range("A1").Select 'move1cell to right

End if
End sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sorry to take so long to respond.
I have changed and i am now trying to work with the following.



If CheckBox1.Value = True Then
Range("A10:Z19").Select
Selection.SpecialCells(xlCellTypeBlanks)(1, 1).Select

Else
Mymsg="Range is full"
End if
End sub

This appears to be working but I have hiden columns with in this range. How can i get it to only select blank cell that are visible?
I found this bit of code but i dont know how to work the two together.
Selection.SpecialCells(xlCellTypeVisible).Select

Thanks for you help!!!
 
Upvote 0
just select the visible cells BEFORE selecting the blank cell, i.e, put

Selection.SpecialCells(xlCellTypeVisible).Select

before

Selection.SpecialCells(xlCellTypeBlanks)(1, 1).Select
 
Upvote 0
I was still searching the board for my soultion when I came across a post from IVAN I made some dumb luck changes and it works if you see somthing wrong with it please shout out.

Sub EXP1()
'
Dim Rg As Range
'// Filtered rg with header = C1:C17
'// Just select one row down
Set Rg = Range("A10:Z19")
'// Assumes Range is Filtered so now get the result
Set Rg = Rg.SpecialCells(xlCellTypeVisible)
'Range("A10:Z19").Select
Rg.SpecialCells(xlCellTypeBlanks)(1, 1).Select
 
Upvote 0

Forum statistics

Threads
1,214,262
Messages
6,118,552
Members
448,835
Latest member
Profast123

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