First cell in range

mucah!t

Well-known Member
Joined
Jun 27, 2009
Messages
593
I tried the following for finding the first cell in range, it seems to work but still gives an error. Does any of you have an idea of what I did wrong


Code:
 Set FirstCl = Range("a1").Start(xlDown).Offset(1, 0)
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Set FirstCl = Range("a1").End(xlDown).Offset(1, 0)
 

mucah!t

Well-known Member
Joined
Jun 27, 2009
Messages
593
Hello VoG,

I try to get the values from the first row to be shown in the textboxes.
The code below doesn't work. When I change End(xlDown) into Start(xlDown), it works, but gives an error. I guess "Start" is an unknown property.
Can you see where I go wrong?


Code:
ListBox1.ListIndex = 1

    Dim FirstCl As Range

    'first data Entry
   Set FirstCl = Range("a1").End(xlDown).Offset(1, 0)    'allow for rows being added deleted above header row

    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = FirstCl.Value
        .TextBox2.Value = FirstCl.Offset(0, 3).Value
        .TextBox3.Value = FirstCl.Offset(0, 4).Value
        .TextBox4.Value = FirstCl.Offset(0, 5).Value
        .TextBox5.Value = FirstCl.Offset(0, 6).Value
    End With
End Sub
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps remove the offset

Code:
Set FirstCl = Range("a1").End(xlDown)
 

mucah!t

Well-known Member
Joined
Jun 27, 2009
Messages
593

ADVERTISEMENT

It now gives the values of the last row?
 

mucah!t

Well-known Member
Joined
Jun 27, 2009
Messages
593

ADVERTISEMENT

Here it is:
Excel Workbook
ABCDEFGH
1RANDNOHLPCHPTRANSWERQUESTIONREMARKDATE
20,67023984325811C'est trs cher.It is very expensive.2-2-2011 18:08
30,44003751612701a peut commencer maintenant.It can start now.2-2-2011 18:08
40,87171972625621cher amidear friend2-2-2011 18:08
50,89764777923201Je voudrais le savoir.I would like to know it.2-2-2011 18:08
60,3787163017601Voulez-vous venir un peu plus tard?Will you come a little later?2-2-2011 18:08
70,069226363701Voulez-vous manger la mme chose?Do you want to eat the same thing?2-2-2011 18:08
80,2975100252901O voulez-vous aller manger?Where do you want to go eat?2-2-2011 18:08
90,463710578401Voulez-vous voir autre chose?Do you want to see something else?2-2-2011 18:08
100,65201115722201Vous n'allez pas le faire.You are not going to do it.2-2-2011 18:08
DATA
Cell Formulas
RangeFormula
A2=RAND()
A3=RAND()
A4=RAND()
A5=RAND()
A6=RAND()
A7=RAND()
A8=RAND()
A9=RAND()
A10=RAND()
C2=IF(AND(B2>=WERKBLAD!$N$2,B2<=WERKBLAD!$O$2+1),MAX($C$1:C1)+1,0)
C3=IF(AND(B3>=WERKBLAD!$N$2,B3<=WERKBLAD!$O$2+1),MAX($C$1:C2)+1,0)
C4=IF(AND(B4>=WERKBLAD!$N$2,B4<=WERKBLAD!$O$2+1),MAX($C$1:C3)+1,0)
C5=IF(AND(B5>=WERKBLAD!$N$2,B5<=WERKBLAD!$O$2+1),MAX($C$1:C4)+1,0)
C6=IF(AND(B6>=WERKBLAD!$N$2,B6<=WERKBLAD!$O$2+1),MAX($C$1:C5)+1,0)
C7=IF(AND(B7>=WERKBLAD!$N$2,B7<=WERKBLAD!$O$2+1),MAX($C$1:C6)+1,0)
C8=IF(AND(B8>=WERKBLAD!$N$2,B8<=WERKBLAD!$O$2+1),MAX($C$1:C7)+1,0)
C9=IF(AND(B9>=WERKBLAD!$N$2,B9<=WERKBLAD!$O$2+1),MAX($C$1:C8)+1,0)
C10=IF(AND(B10>=WERKBLAD!$N$2,B10<=WERKBLAD!$O$2+1),MAX($C$1:C9)+1,0)
G2=IF(COUNTIF(E$2:E$441,E2)>1,"Duplicated","")
G3=IF(COUNTIF(E$2:E$441,E3)>1,"Duplicated","")
G4=IF(COUNTIF(E$2:E$441,E4)>1,"Duplicated","")
G5=IF(COUNTIF(E$2:E$441,E5)>1,"Duplicated","")
G6=IF(COUNTIF(E$2:E$441,E6)>1,"Duplicated","")
G7=IF(COUNTIF(E$2:E$441,E7)>1,"Duplicated","")
G8=IF(COUNTIF(E$2:E$441,E8)>1,"Duplicated","")
G9=IF(COUNTIF(E$2:E$441,E9)>1,"Duplicated","")
G10=IF(COUNTIF(E$2:E$441,E10)>1,"Duplicated","")
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Set FirstCl = IIf(Range("A2").Value = "", Range("A1").End(xlDown), Range("A2"))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,031
Messages
5,856,936
Members
431,841
Latest member
jaybeem

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