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.

 

[johnnyL]

Sub LoopColumn()
'
    Dim c       As Range
    Dim LastRow As Long
'
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
'
    For Each c In Range("A1:A" & LastRow)
        c = Mid(c, 8, 10)
    Next c
End Sub

 

[U4kaDW3Qv1]

This was very helpful. Thank you. Since my data is created dynamically, I never know where the starting column will be, so I needed to modify the code some rather than referring to a fixed column. This worked for me, but I need to test some other scenarios with different versions of the file. But the main thing I needed help with, was the looping, which works perfectly.
-------------------------------------
Sub LoopColumn1()
'
Dim rngMyRange As Range

Range(Selection, Selection.End(xlDown)).Select

Set rngMyRange = Selection
Dim c As Range
'
Range(ActiveCell, ActiveCell.End(xlDown)).Select
'
For Each c In rngMyRange
c = Mid(c, 8, 10)
Next c

'formats date in short date

rngMyRange.NumberFormat = "mm/dd/yyyy"
End Sub
-------------------------------------
Thanks again, Very much appreciated.

 

[johnnyL]

Glad to help @U4kaDW3Qv1

 

[Peter_SSs]

But the main thing I needed help with, was the looping,

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

 

[Peter_SSs]

.. or probably a bit simpler again

Sub ExtractDate_v2()
  With Range(ActiveCell, ActiveCell.End(xlDown))
    .NumberFormat = "mm/dd/yyyy"
    .Replace What:="* ", Replacement:="", LookAt:=xlPart
  End With
End Sub

 

[johnnyL]

@Peter_SSs last code doesn't work.

 

[Peter_SSs]

@Peter_SSs last code doesn't work.

That tells me nothing about what you have and in what way it did not work for you.
Could it be that you forgot to select an appropriate cell before running the code?

It worked for my test data.

 

[johnnyL]

Book1
	A	B
1	11111111/1/2023
2	22222222/1/2023
3	33333333/1/2023
4	44444444/1/2023
5
Sheet1

 

[Peter_SSs]

My suggestions were both based on the format of sample data as provided by the OP.