VBA Range("A" & Rows.count).End(xlUp).Offset(1, 0)

[geloader0]

Hi, Good day. I have data with 55 rows. In this code

Range("A" & Rows.count).End(xlUp).Offset(1, 0)

It starts paste the value in the last available row so it starts from Range("A56"). But I want to paste the value starting from A3 to A55. Any help? Thanks

 

[rollis13]

Use Range("A3"), it will start from A3 instead of A56.

 

[geloader0]

Use Range("A3"), it will start from A3 instead of A56.

Hi. This will result to duplicate value. Anyway, I'm using DO while loop..

This is the code.. Thanks to

shinigamilight​

        Dim d As Date
        d = DateValue("31-dec-22")
        Dim LastDate As Date
        Dim StartDate As Date

        Do
         k = k + 7
         LastDate = DateAdd("d", k, d)
         StartDate = LastDate - 6
         Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = StartDate & " - " & LastDate
        Loop While LastDate <= CDate("1-jan-24")

 

[rollis13]

You can choose to clear your old data adding this line just before the Do :

Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents

or add a row counter to your macro like this and overwrite old data:

Option Explicit
Sub test()
    Dim d As Date
    d = DateValue("31-dic-22")
    Dim LastDate As Date
    Dim StartDate As Date
    Dim k As Long, rw As Long                '<- added
    rw = 3                                        '<- added
    Do
        k = k + 7
        LastDate = DateAdd("d", k, d)
        StartDate = LastDate - 6
        Range("A" & rw) = StartDate & " - " & LastDate '<- changed
        rw = rw + 1                               '<- added
    Loop While LastDate <= CDate("1-gen-24")
End Sub

 

[geloader0]

or add a row counter to your macro like this and overwrite old data:

Option Explicit
Sub test()
    Dim d As Date
    d = DateValue("31-dic-22")
    Dim LastDate As Date
    Dim StartDate As Date
    Dim k As Long, rw As Long                '<- added
    rw = 3                                        '<- added
    Do
        k = k + 7
        LastDate = DateAdd("d", k, d)
        StartDate = LastDate - 6
        Range("A" & rw) = StartDate & " - " & LastDate '<- changed
        rw = rw + 1                               '<- added
    Loop While LastDate <= CDate("1-gen-24")
End Sub

This one much better. Thank you so much

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.

 

[geloader0]

Thanks for the positive feedback, glad having been of some help.

Hi, Good day @rollis13 . Can you help me again?

I want to convert the Weeknumber to a date example from Week 1(start from Sunday), Month 1(January) then year. So the output goes like this " 01/01/2023 - 01/07/2023 "
Please see example below... Thank you very much

Convert Weeknumber from this

To this.

 

[rollis13]

Sorry, I would like to point out that this is a completely different case.
In these cases, to have even a minimum of visibility in the Forum, I suggest starting a new thread with a new appropriate title.