VBA for excel requirement

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

Answer 1

What does your code look like?

Edit:  Same question is posted in the Answers/Office/Excel forum.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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

Answer 7

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.

Answer 8

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.

Answer 9

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.

Answer 10

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.