Find used cells, skip blanks

[Petrogeo]

Hi, guys.

I have a small problem, how can I find just used cells on a column? I sending an attached file as example. Pressing the Edit/Change button, opens a form. Please look at a combobox values, note that there is blank "values", between 13 and 14, like the B column on the sheet. Is it possible just to show the number in the combo box?? without blank "values"???????


Thanks in advance

[Simon Lloyd]

I suppose the easiest way would be to follow this link http://vbaexpress.com/forum/showthread.php?t=11630 the functions shown here create and sort a list of items for combobox1 so your blanks would be at the bottom.

Regards,
SImon

[mdmackillop]

[VBA]Private Sub UserForm_Activate()
Dim Rng As Range, c As Range
Set Rng = Range(Cells(7, 2), Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeConstants, 1)
For Each c In Rng
cboSeq.AddItem c.Value
Next
Range("A2").Select
Set Rng = Nothing
Set c = Nothing
End Sub
[/VBA]

[Petrogeo]

Thanks Simon for the response and link, but I think that the code is very complicated, I'm not a VBA expert, I confess.

It doesn't exist a easiest code to incorporate in my workbook??

Rgds,
Petrogeo

[Simon Lloyd]

Malcom has posted a very simple to understand code which you just paste into your UserForm code sheet!

Regards,
SImon

[Petrogeo]

Hi,

The code posted by malcom worked very well, thanks. But the problem is that when I select Seq Number in combo box, its row data is not coming to the textBox on the Userform. Do you know guys what a I mean??

rgds,
Petrogeo

[Simon Lloyd]

Which textbox? which number?, originally you didn't say you had a problem getting data to a textbox just sorting the data in the combobox!

Regards,
SImon

[mdmackillop]

You appear to have textboxes in your code which are not on the form. Try stepping through the combobox change code.

[mdmackillop]

My code does not use the RowSource property so this test is failing.
[VBA]If (cboSeq.RowSource <> vbNullString) Then[/VBA]

[Petrogeo]

Hi.

Simon, I didn't say because didn't have problem. So it happened when I used the code provided by Malcom. I'm sending the attached file as example.

Any other idea to solve this new problem?? When I sort data on the combobox can't get data to a text box.

rgds,

Petrogeo

[mdmackillop]

Here's and adjusted version of your code in the attachment.

[mdmackillop]

Your code can be simplified by using Find with Offset to locate the first cell in the desired row, then simple offsets from that cell. Also, use SelectCase rather than multiple If statements
[vba]Private Sub cboSeq_Change()
Dim SourceRange As Excel.Range
Dim c As Range
Dim Val1 As String, Val2 As String, Val3 As String, Val4 As String, Val5 As String, _
Val6 As String, Val7 As String, Val8 As String, Val9 As String, Val10 As String, _
Val11 As String, Val12 As String, Val13 As String, Val14 As String

Set c = Columns(2).Find(cboSeq.Value, LookIn:=xlValues).Offset(, -1)

Val1 = cboSeq.Value

'Get the value of the second column
Val2 = c.Offset(, 2).Value
Val3 = c.Value
Val4 = c.Offset(, 4).Value
Val5 = c.Offset(, 9).Value
Val6 = c.Offset(, 10).Value
Val7 = c.Offset(, 11).Value
Val8 = c.Offset(, 12).Value
Val9 = c.Offset(, 5).Value
Val10 = c.Offset(, 6).Value
Val11 = c.Offset(, 8).Value
Val12 = c.Offset(, 43).Value
Val13 = c.Offset(, 3).Value
Val14 = c.Offset(, 7).Value
Val15 = c.Offset(, 13).Value
Val16 = c.Offset(, 15).Value
Val17 = c.Offset(, 16).Value
Val18 = c.Offset(, 18).Value

txtLinha.Value = Val2
txtData.Value = Val3
txtDir.Value = Val4
txtFSP.Value = Val5
txtLCSP.Value = Val6
txtLSP.Value = Val7
txtMSP.Value = Val8
txtSOL.Value = Val9
txtEOL.Value = Val10
cboCabos.Value = Val11
txtComentarios.Value = Val12
'TextBox1.Value = Val15
'TextBox2.Value = Val16
'TextBox3.Value = Val17
'TextBox4.Value = Val18


Select Case Val13
Case "Prime"
optPrime.Value = True
Case Is = "Infill"
optInfill.Value = True
Case "Reshoot"
optReshoot.Value = True
End Select

Select Case Val14
Case "C"
optCompleta.Value = True
Case "I"
optIncompleta.Value = True
Case "NTBP"
optNTBP.Value = True
End Select

'Clean Up
Set SourceRange = Nothing
End Sub[/vba]

[Petrogeo]

Thanks MD!!! your file attached is corrupted for me, I don't know why... but I'm trying to apply your code sample.

many thanks,
Petrogeo

[mdmackillop]

Hi Petrogeo,
Unfortunately losing the RowSource caused more problems than I realised. I've adjusted your code to remove this variable. Check to make sure I got all the cell references right!

[Petrogeo]

Thanks MD!!!!!!!!!!!
It works very well. I'm doing some adjustments on the references.

,
Petrogeo