Need a little help with vba: Please advice on range selection till last row from given address.

[pedie]

Hi, Last line in the code is where i need help...

After finding and setting found adderss as address i want vba to select that range and copy it to sheet2.range("A1") as values.


Thanks alot for helping

[/FONT]
[FONT=Courier New]Dim startdate As String
Dim enddate As String
Dim myfirstadd, mysecondadd As String[/FONT]
[FONT=Courier New]startdate = Me.DTPicker1.Value
enddate = Me.DTPicker2.Value[/FONT]
[FONT=Courier New]Dim c As Range
With Sheet1.Range("a1:as1")
Set c = .Find(startdate, LookIn:=xlValues)
If c Is Nothing Then
MsgBox startdate & " not found"
Exit Sub
Else
myfirstadd = c.Address
End If
End With[/FONT]
[FONT=Courier New]With Sheet1.Range("a1:as1")
Set c = .Find(enddate, LookIn:=xlValues)
If c Is Nothing Then
MsgBox startdate & " not found"
Exit Sub
Else
mysecondadd = c.Address
End If
End With[/FONT]
[FONT=Courier New]dim lr as long[/FONT]
[FONT=Courier New]lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New][B]Range(myfirstadd & ":" & mysecondadd & Lr).Select[/B][/FONT]
[FONT=Courier New]

 

[VoG]

Hi. Try

Range(myfirstadd & ":" & mysecondadd & Lr).Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[pedie]

Hi Peter, that way it selects whole col till lastrow including blanks....

Thanks again!

 

[VoG]

Try

Rows(LR).Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[Norie]

pedie

What range do you actually want to copy?

You seem to be looking for the start and end date in row 1.

Which row(s) do you want to copy once you've found them?

Is it from the start date down to the lastrow in that column and across the the end date?

eg if start date is in column C, end date is in column L and lastrow is 85 then copy C1:L85

 

[pedie]

It seach row 1, dates are in row 1.
For example first found = L1, second found = P1, i want to select from L1:P & lr.

Thanks again.
Rows(Lr) select the last row only...
I know my explaination does not make sense at all sometimes

 

[VoG]

So is it?

Range("L1:P" & LR).Copy
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[Norie]

Pedie

That's basically what I was saying.

Try this which worked for me.

Option Explicit
Sub test()
Dim startdate As String
Dim enddate As String
Dim myfirstadd As String, mysecondadd As String
Dim lr As Long
Dim c As Range
 
    startdate = Me.DTPicker1.Value
    enddate = Me.DTPicker2.Value
    
    '    startdate = DateSerial(2011, 5, 1)
    '    enddate = DateSerial(2011, 5, 4)
    
    With Sheet1.Range("A1:AS1")
        Set c = .Find(startdate, LookIn:=xlValues)
        If c Is Nothing Then
            MsgBox startdate & " not found"
            Exit Sub
        Else
            myfirstadd = c.Address
        End If
    End With
 
    With Sheet1.Range("A1:AS1")
        Set c = .Find(enddate, LookIn:=xlValues)
        If c Is Nothing Then
            MsgBox startdate & " not found"
            Exit Sub
        Else
            mysecondadd = c.Address
        End If
    End With
 
    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
 
    Range(myfirstadd, Cells(lr, c.Column)).Copy
 
End Sub

 

[pedie]

Norie, thank you very much! that's is what I was looking for...
Peter, thanks for helping...
Thanks again.

 

[pedie]

Sorry to bring this up again but why is it that if i dont format it to general (I mean the dates in row1) it return not found even if the dates are actually there...

Thanks again

Or let me put this way, what is the best way to search for dates using vba..