locked
VBA for excel requirement RRS feed

  • Question

  • Hi,

     

    I need the VBA codes for the below two conditions.

     

    1. Delete the entire row based on a comment in the given cell of a column for example if there is  any cell in Column B contains the text "Test", then all the rows would be deleted.

     

    2. Copy a specific no of columns from Sheet1 to Sheet2 based on the column headings names, for example if the columns in Sheet1 having heading names "Test1", "Test2" and "Test3", then these three columns would be copied to Sheet2.

     

    Regards,

    RKP


    Radhakant

    Monday, June 17, 2013 9:04 AM

Answers

  • Hello,

    Code snippet for your reference:

    ---------------

    1. This will delete all rows when there is a cell in column B contains the text "Test".

    Dim ColB As Range
    Set ColB = Range("B1:B" & Range("B:B").End(xlDown).Row)
    For Each cell In ColB
        If cell.Value = "Test" Then
            Rows("1:" & ColB.Rows.Count).Delete
            Exit For
        End If
    Next cell

    If you just want to delete the row where "Test" is, change this line

    Rows("1:" & ColB.Rows.Count).Delete

    to

    Rows(cell.Row).Delete

    ---------------

    2. This will copy the columns in Sheet1 whose "heading names" are "Test1", "Test2" and "Test3" to Sheet2's column A, B and C.

    Dim firstCell As Range
    Set firstCell = Sheet1.Rows(3).Find(What:="Test1", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(1))
    
    Set firstCell = Sheet1.Rows(3).Find(What:="Test2", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(2))
    
    Set firstCell = Sheet1.Rows(3).Find(What:="Test3", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(3))

    Also, you can use Range.Insert Method (Excel) to locate the column in Sheet2.

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    Tuesday, June 18, 2013 8:14 AM
  • Hi,

    1. You can use Range.Find Method (Excel) to find the "Test" and "Test1":

    Dim Test As Range
    Dim Test1 As Range
    
    Set Test = Columns("B").Find("Test")
    Set Test1 = Columns("C").Find("Test1")
    
    If Not Test Is Nothing And Not Test1 Is Nothing Then
        Dim rowCount As Integer
        rowCount = Columns("B").End(xlDown).Row
        Rows("1:" & rowCount).Delete
    End If

    2. Since I cannot see your workbook, I am unable to find the reason why it does not work. The code snippet I provide is just for your reference, may not work for your workbook if without modification.

    Could you make it more detailed? Is there any error message?

    Or you can share the Workbook with us via SkyDrive: https://skydrive.live.com/

    Thanks,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    Tuesday, June 18, 2013 1:39 PM
  • Hi Damon,

    I am using the below VBA and i am getting the yellow debug error in the row 3 (highlighted with --- sing)

    Sub Copy()
    Dim firstCell As Range
    Set firstCell = Sheet1.Rows(3).Find(What:="TYPE", LookIn:=Excel.xlValues)
    ---Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(1))
    Set firstCell = Sheet1.Rows(3).Find(What:="USER_NAME_APPLIEDBY", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(2))
    Set firstCell = Sheet1.Rows(3).Find(What:="PAYMENT_METHOD", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(3))
    End Sub


    Radhakant

    Hi,

    Thanks for coming back.

    Set firstCell = Sheet1.Rows(3).Find(What:="TYPE", LookIn:=Excel.xlValues)

    This line searches the text "TYPE" in the third row of the worksheet. Does the third row contains the text "TYPE"? If not, "firstCell" will be "Nothing" and the following line will throw an error. If the "heading names" are not in the third row, change "Row(3)" to the row where they are.

    Also, it would be better to add an "If" statement to handle it (Sorry, I forgot to add it in the previous reply):

    Set firstCell = Sheet1.Rows(3).Find(What:="Test1", LookIn:=Excel.xlValues)
    If Not firstCell Is Nothing Then
        Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(1))
    End If
    Thanks,

    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    Wednesday, June 19, 2013 7:06 AM
  • Hi,

    Also in the 1st VBA i am getting yellow d bug error in ---Rows(cell.Row).Delete

    Dim Test As Range

    Dim Test1 As Range

    Set Test = Columns("B").Find("Test")

    Set Test1 = Columns("C").Find("Test1")

    If Not Test Is Nothing And Not Test1 Is Nothing Then

        Dim rowCount As Integer

        rowCount = Columns("B").End(xlDown).Row

       ---Rows(cell.Row).Delete

    End If


    Radhakant

    Where do you define the "cell"? "Cell" is not an object in Excel Oject Modals. It must be defined before used.

    You can use Worksheet.Range Property (Excel) to define the cell:

    Dim cell As Range
    Set cell = Sheet1.Range("A1") 'cell represents A1 in Sheet1

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:29 PM
    Wednesday, June 19, 2013 7:14 AM
  • Furthermore, MSDN documents will help you with VBA practicing.

    For your reference:

    Getting Started with VBA in Excel 2010

    Excel Object Model Reference

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    Wednesday, June 19, 2013 7:17 AM

All replies

  • What does your code look like?

    Edit:  Same question is posted in the Answers/Office/Excel forum.
    • Edited by James Cone Monday, June 17, 2013 11:41 AM
    • Marked as answer by Update Date Thursday, June 20, 2013 3:30 PM
    • Unmarked as answer by Damon Zheng Friday, June 21, 2013 5:23 AM
    Monday, June 17, 2013 11:23 AM
  • Hello,

    Code snippet for your reference:

    ---------------

    1. This will delete all rows when there is a cell in column B contains the text "Test".

    Dim ColB As Range
    Set ColB = Range("B1:B" & Range("B:B").End(xlDown).Row)
    For Each cell In ColB
        If cell.Value = "Test" Then
            Rows("1:" & ColB.Rows.Count).Delete
            Exit For
        End If
    Next cell

    If you just want to delete the row where "Test" is, change this line

    Rows("1:" & ColB.Rows.Count).Delete

    to

    Rows(cell.Row).Delete

    ---------------

    2. This will copy the columns in Sheet1 whose "heading names" are "Test1", "Test2" and "Test3" to Sheet2's column A, B and C.

    Dim firstCell As Range
    Set firstCell = Sheet1.Rows(3).Find(What:="Test1", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(1))
    
    Set firstCell = Sheet1.Rows(3).Find(What:="Test2", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(2))
    
    Set firstCell = Sheet1.Rows(3).Find(What:="Test3", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(3))

    Also, you can use Range.Insert Method (Excel) to locate the column in Sheet2.

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    Tuesday, June 18, 2013 8:14 AM
  • Hi Damon,

    The 1st one is working and if i want to extend it to another column C to delete the row contains "Test1". I mean i want to delete the rows if column B contains "Test" and column C contains "Test1".

    2nd vba is not working...

    Regards,

    Radhakant


    Radhakant

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    • Unmarked as answer by Damon Zheng Friday, June 21, 2013 5:23 AM
    Tuesday, June 18, 2013 10:36 AM
  • Hi,

    1. You can use Range.Find Method (Excel) to find the "Test" and "Test1":

    Dim Test As Range
    Dim Test1 As Range
    
    Set Test = Columns("B").Find("Test")
    Set Test1 = Columns("C").Find("Test1")
    
    If Not Test Is Nothing And Not Test1 Is Nothing Then
        Dim rowCount As Integer
        rowCount = Columns("B").End(xlDown).Row
        Rows("1:" & rowCount).Delete
    End If

    2. Since I cannot see your workbook, I am unable to find the reason why it does not work. The code snippet I provide is just for your reference, may not work for your workbook if without modification.

    Could you make it more detailed? Is there any error message?

    Or you can share the Workbook with us via SkyDrive: https://skydrive.live.com/

    Thanks,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    Tuesday, June 18, 2013 1:39 PM
  • Hi Damon,

    I am using the below VBA and i am getting the yellow debug error in the row 3 (highlighted with --- sing)

    Sub Copy()
    Dim firstCell As Range
    Set firstCell = Sheet1.Rows(3).Find(What:="TYPE", LookIn:=Excel.xlValues)
    ---Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(1))
    Set firstCell = Sheet1.Rows(3).Find(What:="USER_NAME_APPLIEDBY", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(2))
    Set firstCell = Sheet1.Rows(3).Find(What:="PAYMENT_METHOD", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(3))
    End Sub


    Radhakant

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    • Unmarked as answer by Damon Zheng Friday, June 21, 2013 5:23 AM
    Tuesday, June 18, 2013 3:41 PM
  • Hi,

    Also in the 1st VBA i am getting yellow d bug error in ---Rows(cell.Row).Delete

    Dim Test As Range

    Dim Test1 As Range

    Set Test = Columns("B").Find("Test")

    Set Test1 = Columns("C").Find("Test1")

    If Not Test Is Nothing And Not Test1 Is Nothing Then

        Dim rowCount As Integer

        rowCount = Columns("B").End(xlDown).Row

       ---Rows(cell.Row).Delete

    End If


    Radhakant

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    • Unmarked as answer by Damon Zheng Friday, June 21, 2013 5:23 AM
    Tuesday, June 18, 2013 4:07 PM
  • Hi Damon,

    I am using the below VBA and i am getting the yellow debug error in the row 3 (highlighted with --- sing)

    Sub Copy()
    Dim firstCell As Range
    Set firstCell = Sheet1.Rows(3).Find(What:="TYPE", LookIn:=Excel.xlValues)
    ---Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(1))
    Set firstCell = Sheet1.Rows(3).Find(What:="USER_NAME_APPLIEDBY", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(2))
    Set firstCell = Sheet1.Rows(3).Find(What:="PAYMENT_METHOD", LookIn:=Excel.xlValues)
    Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(3))
    End Sub


    Radhakant

    Hi,

    Thanks for coming back.

    Set firstCell = Sheet1.Rows(3).Find(What:="TYPE", LookIn:=Excel.xlValues)

    This line searches the text "TYPE" in the third row of the worksheet. Does the third row contains the text "TYPE"? If not, "firstCell" will be "Nothing" and the following line will throw an error. If the "heading names" are not in the third row, change "Row(3)" to the row where they are.

    Also, it would be better to add an "If" statement to handle it (Sorry, I forgot to add it in the previous reply):

    Set firstCell = Sheet1.Rows(3).Find(What:="Test1", LookIn:=Excel.xlValues)
    If Not firstCell Is Nothing Then
        Sheet1.Columns(firstCell.Column).Copy (Sheet2.Columns(1))
    End If
    Thanks,

    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    Wednesday, June 19, 2013 7:06 AM
  • Hi,

    Also in the 1st VBA i am getting yellow d bug error in ---Rows(cell.Row).Delete

    Dim Test As Range

    Dim Test1 As Range

    Set Test = Columns("B").Find("Test")

    Set Test1 = Columns("C").Find("Test1")

    If Not Test Is Nothing And Not Test1 Is Nothing Then

        Dim rowCount As Integer

        rowCount = Columns("B").End(xlDown).Row

       ---Rows(cell.Row).Delete

    End If


    Radhakant

    Where do you define the "cell"? "Cell" is not an object in Excel Oject Modals. It must be defined before used.

    You can use Worksheet.Range Property (Excel) to define the cell:

    Dim cell As Range
    Set cell = Sheet1.Range("A1") 'cell represents A1 in Sheet1

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:29 PM
    Wednesday, June 19, 2013 7:14 AM
  • Furthermore, MSDN documents will help you with VBA practicing.

    For your reference:

    Getting Started with VBA in Excel 2010

    Excel Object Model Reference

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Update Date Thursday, June 20, 2013 3:31 PM
    Wednesday, June 19, 2013 7:17 AM
  • Hi,

    Thanks again for coming back. I really appreciate you mark all my replies as answer. I'm happy that they can help you. But for the convenience of all community members, please don't mark the reply which does not contain solution or helpful information for resolving the issue.

    I have unmarked some of the replies. Your cooperation and understanding will be greatly appreciated.

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Edited by Damon Zheng Friday, June 21, 2013 5:59 AM modify
    Friday, June 21, 2013 5:58 AM