VBA Delete Entire Rows Based On The Value Of Single Cell

[elyanivson]

Hi,
I want to do exactly what my title sais.

I have a spreadsheet with 8 columns and 16000 rows.

I want to delete each row that the value in the first column (A) is "James".

I need to create a vba code.

Thank you in advance.

[GTO]

Assuming that there is a header row, try:
[VBA]Sub exa1()
Dim lLRow As Long

With Sheet3 'or ThisWorkbook.Worksheets ("Sheet3")
lLRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A:A").AutoFilter Field:=1, Criteria1:="James"
.Range("A2:A" & lLRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp
.AutoFilterMode = False
End With
End Sub[/VBA]

[elyanivson]

Thanks!
Works Perfect.
I have another issue that I need help with,please.

Now, I need to copy from one xslx file all the active cells and paste them into another xslx file ,to the first row that does not include any value(paste them into the second file).

Thnak you again.

[GTO]

I am afraid you lost me. What does "all the active cells" mean?

Also - we need more details I think. Are both .xlsx workbooks already open when we start this? Are we copying from just one sheet or many? What are the sheet names, etc.

[elyanivson]

O.K
First, I'm sorry that I didn't provide al the necessary details.

When I say "all the active cells", I mean : ROW 1 and 2, because row 3 has no values.

A B
ID NAME

1 555 James


2 444 Rooney

3

There is the first xslx file(Names) and the second is also xlsx file(Orders).
both are xslx.
The "Names" file is not open ,while the "Orders" file is Open.
I want to copy the data from the Names to the orders.
I want to copy the data to the first row that does not contain data in Orders file.

Thanks.

[GTO]

...Are we copying from just one sheet or many?

What are the sheet names, etc.

Sorry, you still are not saying whether we are copying from many sheets in Names.xlsx or from many sheets. See, that would change how we would write the code, so it is best to paint us the clearest picture you can, of what the code is truly to do.

[elyanivson]

ok.
I'll try to do my best explaining the process.
Let's say I have 2 Files.
First file=Names.xls
Second file=Orders.xls

Each file include one sheet.

the name of the sheet in names.xls is: "First"
the name of the sheet in orders.xls is: "Second"

The "names.xls" file is open,while the "Orders.xls" is closed.

I would like to run the vba from the names.xls , which means I want to open the orders.xls file and copy all the data from the "First" worksheet(except the header row).
Then, I would like to switch again to the names.xls file, and paste all the data I just copied, to the First empty row in names.xls, in "second" worksheet.

I Hope It's clear now.I tried my best
Thanks again.

[elyanivson]

Hi again,
I tried to do the process by my self but I'm getting an error.
Those are the functions and the Error.

[vba]
Sub Copy_From_Report_TO_Names()
Dim wbk As Workbook
strFirstFile = "C:\Documents and Settings\yanive\Desktop\report.xls"
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Report")
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
End With
Set wbk = Windows("IMPORT_LPR03102011.xls").Activate
Dim aa As Range
Set aa = SelectFirstNull.SelectFirstNullAfterDeleteToCopyThereTheDataFromReport.Sele ct
With wbk.Sheets("Second")
Range(aa).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With[/vba]

The Function "SelectFirstNullAfterDeleteToCopyThereTheDataFromReport"
is in other module.(SelectFirstNull)
and this is what the function includes:

[vba]Sub SelectFirstNullAfterDeleteToCopyThereTheDataFromReport()
Dim BCell, NBCell
Range("A1").Select
For i = 1 To 65536
If ActiveCell.Value = Empty Then
BCell = "A" & CStr(i - 1)
NBCell = "A" & CStr(i - 2)
Exit Sub
Else
Range("A" & CStr(i + 1)).Select
End If
Next i
End Sub[/vba]
This one works perfect.
but, the Error i get is on the line in red.
the error is:
"Compile Error- Expected Function or Variable."

I hope you can help me with it. Thank you for your help!

[GTO]

[vba]Set aa = SelectFirstNull.SelectFirstNullAfterDeleteToCopyThereTheDataFromReport.Select[/vba]

The Function "SelectFirstNullAfterDeleteToCopyThereTheDataFromReport"
is in other module.(SelectFirstNull)
and this is what the function includes:

Sub SelectFirstNullAfterDeleteToCopyThereTheDataFromReport()

Thank you for the clarifications thus far.

Okay, as I am reading it, I see a couple of issues.

• SelectFirstNull.SelectFirstNullAfterDeleteToCopyThereTheDataFromReport is fine to call a Sub or Function in the module SelectFirstNull.
• But... SelectFirstNullAfterDeleteToCopyThereTheDataFromReport is not a function, it is a sub. Thus, it cannot return anything (whether an object or a value etc) like a function does. (Arguments passed by reference can return, but let us not think about that for the moment. Hence, we get the error, as Excel is stating that it expected a function.
• IF it was a function being called, we still have issues, as the Set statement is used to set a reference to an Object, but you see that you have it ending in .Select. This would foul no matter what.

Please consider making us a couple of sample workbooks (for Names.xls and Orders.xls) with non-sensitive data, that accurately portray data layout and data type of the real workbooks. You can put both into a .zip folder and attach them with your next post. I think this would make it easier for us to help.

Mark