Selecting rows in excel

Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi,
Column A has several numbers ranging from single digit to seven digit ones. I am only looking for rows which have 4 digits numbers. These rows have to be copied and pasted into a separate worksheet.
I do not want to sort because each row is a child of the previous row.
Can anyone help me with a macro for this activity.
Also, I don't know how to open VBA editor on my Excel (2007 version). Where is it located in the menu?
Thanks,
Nic

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Assumptions
1. Data is on sheet1
2. rows are to be copied on sheet2

Sub moveRows()

Dim lMaxRows As Long
Dim rowIdx As Long
Dim inString As String
Dim lbeanCounter As Integer

    Sheets("Sheet1").Select
    
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    lbeanCounter = 1
    
    For rowIdx = 2 To lMaxRows
    
        Sheets("Sheet1").Select

        inString = Trim(Cells(rowIdx, "A"))
        
        If Len(inString) = 4 Then
            
            lbeanCounter = lbeanCounter + 1
            
            Rows(rowIdx).Select
            Selection.Copy
            
            Sheets("Sheet2").Select
            Rows(lbeanCounter).Select
            ActiveSheet.Paste
        End If
    Next
    
End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month