Looping thru cels in a dynamic range

[U4kaDW3Qv1]

I have a range (2 columns) that will be dynamic (meaning the columns the data is in will be variable AND the length in number of rows in the range will also be variable).

I have a short bit of code (see below) that is intended to strip out leading text from the first column, leaving only the date that is in the traiing part of the cell. This is in a Sub that would be called through the loop (I'd imagine) to go through all the cells i this dynamic range.

----------------------------------

Sub DoIt()

'Beginning in active cell, offsets results by two columns, then strips out leading text and returns the text eginning at character 8 and returns the next 10 characters.

' this allows a date with up to suceeding 10 characters to be returned.

ActiveCell.Offset(0, 0) = Mid(ActiveCell, 8, 10)

End Sub

----------------------------------

The data looks like this. with the first two rows appearing as I would like after applying the code above and the succeeding rows as they would be beforehand.

Date Amount 1/1/2023	36801.03
2/1/2023	21912.81
Sum of 3/1/2023	19369.54
Sum of 4/1/2023	29971.53

But, I have been uable to get a looping macro that goes through this range to work (and I've searched and tried a lot).

Any sample code you could suggest would be appreciated.
Thank you.

 

[U4kaDW3Qv1]

What about doing them all at once instead of looping a row at a time?

Sub ExtractDate()
  With Range(ActiveCell, ActiveCell.End(xlDown))
    .NumberFormat = "mm/dd/yyyy"
    .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(7, 1))
  End With
End Sub

This looks more efficient than looping by putting them into an array. I will give it a try and Thank you!

 

[U4kaDW3Qv1]

What about doing them all at once instead of looping a row at a time?

Sub ExtractDate()
  With Range(ActiveCell, ActiveCell.End(xlDown))
    .NumberFormat = "mm/dd/yyyy"
    .TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(7, 1))
  End With
End Sub

This worked very well and was extremely fast with he data going into an array, Thanks so much and I learned something new!

 

[Peter_SSs]

This worked very well and was extremely fast ... Thanks so much and I learned something new!

You're welcome. Glad it worked for you. Thanks for the follow-up.

...with he data going into an array,

For the record though, neither of my suggestions involved putting the data into an array.

 

[johnnyL]

@Peter_SSs Your code clearly mentions array.

 

[Peter_SSs]

@Peter_SSs Your code clearly mentions array.

The relevance of that?