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)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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"))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top