how to programatically do lot of steps

Thanks for your reply.

Pls. tell me what do you mean by "Apply filter to your sheet"... i tried it as this that i press the filter button but nothing happens. Me using Excel 2007.

Originally Posted by Marcol

Have you tried this

Apply a filter to your sheet

With column F

Text filters > Begins with

Show Rows Where:
Begins with:= a

Select the "or" radio button
then in the next 2 fields
Begins with:= A

Select all the resultant rows
Copy and Paste to new sheet

Once again thanks for your reply.

Pls. help me to do these kind of steps programatically. I am more interested in VBA code for this.

Originally Posted by Marcol

Have you tried this

Apply a filter to your sheet

With column F

Text filters > Begins with

Show Rows Where:
Begins with:= a

Select the "or" radio button
then in the next 2 fields
Begins with:= A

Select all the resultant rows
Copy and Paste to new sheet

Also i would appreciate if anyone can guide me to some good tutorial on learning the excel programming.

Thanks for your reply. Here is a sample workbook.

I am interested in parsing out data from this type of sheets in following steps.

1. Delete all ROWS in which the column AB is empty.
2. Select all ROWS in which the first letter of the text in column AB is "A".
3. Copy those ROWS and paste into separate sheet.

Originally Posted by royUK

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Hi,

Try the following:

Public Sub CopyRows()
    Dim lRow As Long
    Dim lLastRow As Long
    Dim lTargetRow As Long
    Dim wksData As Excel.Worksheet ' data sheet
    Dim wksTarget As Excel.Worksheet ' target sheet
    Const iCOL As Integer = 6 ' column F
    Const lFIRST_ROW As Integer = 2 ' first data row (change as appropriate)
    
    Set wksData = ThisWorkbook.Sheets(1) ' change as appropriate
    Set wksTarget = ThisWorkbook.Sheets.Add
    
    With wksData
    
        ' get the last used row
        lLastRow = .Cells(.Rows.Count, iCOL).End(xlUp).Row
        
        ' loop through used rows
        lTargetRow = 1
        For lRow = lFIRST_ROW To lLastRow
            
            ' check whether the cell value starts with "a" and paste to the
            ' target row on the target sheet if so
            If Left$(Trim$(.Cells(lRow, iCOL)), 1) = "a" Then
                .Rows(lRow).EntireRow.Copy
                wksTarget.Cells(lTargetRow, 1).PasteSpecial , Paste:=xlAll
                lTargetRow = lTargetRow + 1
            End If
            
        Next
    
    End With
    
    Set wksData = Nothing
    Set wksTarget = Nothing
    
End Sub

Thanks,
Dom

Thanks a lot DOM for your code. It is very helpfull. Would you be kind enough to write comments in each line of your code to explain what is it doing? It will greatly help me in the understanding of the code working also.

Thanks again.

Originally Posted by Dom83

Hi,

Try the following:

Public Sub CopyRows()
    Dim lRow As Long
    Dim lLastRow As Long
    Dim lTargetRow As Long
    Dim wksData As Excel.Worksheet ' data sheet
    Dim wksTarget As Excel.Worksheet ' target sheet
    Const iCOL As Integer = 6 ' column F
    Const lFIRST_ROW As Integer = 2 ' first data row (change as appropriate)
    
    Set wksData = ThisWorkbook.Sheets(1) ' change as appropriate
    Set wksTarget = ThisWorkbook.Sheets.Add
    
    With wksData
    
        ' get the last used row
        lLastRow = .Cells(.Rows.Count, iCOL).End(xlUp).Row
        
        ' loop through used rows
        lTargetRow = 1
        For lRow = lFIRST_ROW To lLastRow
            
            ' check whether the cell value starts with "a" and paste to the
            ' target row on the target sheet if so
            If Left$(Trim$(.Cells(lRow, iCOL)), 1) = "a" Then
                .Rows(lRow).EntireRow.Copy
                wksTarget.Cells(lTargetRow, 1).PasteSpecial , Paste:=xlAll
                lTargetRow = lTargetRow + 1
            End If
            
        Next
    
    End With
    
    Set wksData = Nothing
    Set wksTarget = Nothing
    
End Sub

Thanks,
Dom