Previous Topic Next Topic

Selecting rows in excel

Closed
Nic - Mar 26, 2010 at 06:48 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 26, 2010 at 11:02 AM
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

Answer 1 / 1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Mar 26, 2010 at 11:02 AM
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