Selecting rows in excel Closed

Question

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 

Configuration: Windows XP / Excel 2007

rizvisa1 · Answer

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

Selecting rows in excel

1 response

Similar discussions

Subscribe To Our Newsletter!