PDA

View Full Version : Find used cells, skip blanks



Petrogeo
02-25-2007, 11:51 AM
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"???????
:banghead:

Thanks in advance
:help

Simon Lloyd
02-25-2007, 01:25 PM
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
02-25-2007, 03:53 PM
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

Petrogeo
02-25-2007, 03:53 PM
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
02-26-2007, 03:20 AM
Malcom has posted a very simple to understand code which you just paste into your UserForm code sheet!

Regards,
SImon

Petrogeo
02-26-2007, 08:04 AM
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
02-26-2007, 11:30 AM
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
02-26-2007, 12:19 PM
You appear to have textboxes in your code which are not on the form. Try stepping through the combobox change code.

mdmackillop
02-26-2007, 12:23 PM
My code does not use the RowSource property so this test is failing.
If (cboSeq.RowSource <> vbNullString) Then

Petrogeo
02-26-2007, 02:07 PM
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
02-26-2007, 02:53 PM
Here's and adjusted version of your code in the attachment.

mdmackillop
02-26-2007, 02:57 PM
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
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

Petrogeo
02-27-2007, 03:19 AM
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
02-27-2007, 01:43 PM
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
02-27-2007, 05:55 PM
Thanks MD!!!!!!!!!!!
It works very well. :thumb I'm doing some adjustments on the references.

:beerchug: ,
Petrogeo