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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
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!!!
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
just select the visible cells BEFORE selecting the blank cell, i.e, put

Selection.SpecialCells(xlCellTypeVisible).Select

before

Selection.SpecialCells(xlCellTypeBlanks)(1, 1).Select
 

Lowell In the south

Board Regular
Joined
Sep 26, 2002
Messages
55
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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