Deleting Row in Excel VBA

[Cyberpawz]

Here is my code, I am attempting to delete only rows in the second sheet in the workbook called RawData. This code will delete rows in the active sheet, the problem is that the button is on page 1, not page 2... I want the data to stay on page 2.

Please Login or Register  to view this content.

[jraj1106]

Hi
Try this:

Sub Deleterows()
sheets("RawData").select
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

[Whizbang]

No need to use the select statement.

Please Login or Register  to view this content.

[Cyberpawz]

No need to use the select statement.

Please Login or Register  to view this content.

So this will look in Column C and delete the row if empty?

Sounds good, is there a way to make it so I can change the variable upon what is in the cell?

[Whizbang]

Try this to customize the variable:

Please Login or Register  to view this content.

Change to suit.

[Cyberpawz]

Ok, I look at that, how does this only select one column to look into and delete a row if a word is in said dedicated column?

[Whizbang]

Short answer is "The field argument".

The long asnwer is:

Please Login or Register  to view this content.

This bit just defines the table. So, it is applying autofilter to columns A:H down to the last used row in A1. Assuming that A1 has contiguous data down to row 200, the range would be A1:H200. You would need to modify the columns to suit your workbook.

Please Login or Register  to view this content.

This is the bit that identifies which column to compare values. 3 is the third column of the table defined (A1:H200). If the table was B1:J200 and you wanted to search column C, you would use Field:=2.

Please Login or Register  to view this content.

This is where you define the variable you want to match. This can actually be fairly complex. You can use wildcards to make a "contains" type of evaluation, or you could do a less than or greater than scenario.

Please Login or Register  to view this content.

This bit is fairly straight forward in that it deletes the visible rows in the table. One thing to notice, though, is that I started the table at A2, not A1. This is because the header row would be visible, and we don't want to delete that. Also, I don't need to check any columns other than A. If A is visible, then all columns in that row are visible.

Please Login or Register  to view this content.

This bit just turns Autofilter off for the table.

[Cyberpawz]

Is there a way to simplify it? I only ask because if I come back to it in a month I want to be able to come back to it and see with ease that line x defines the column, line y defines what it is looking for.

[Whizbang]

Please Login or Register  to view this content.

There, now all your criteria is set up at the top of the procedure and you don't need to worry about figuring the rest out.

[Cyberpawz]

Why put a limitation on first and last column, wouldn't it be easier to have something along the lines of

Please Login or Register  to view this content.

[Whizbang]

Please Login or Register  to view this content.

[Cyberpawz]

I was thinking something along the lines of this: I know this probably won't work but you'll get the idea.

Please Login or Register  to view this content.

[Whizbang]

This is about as simple as I can make it.

Please Login or Register  to view this content.

Even though UsedRange can be used to find the last row in a sheet, it is only updated when the workbook is saved. So if rows have been added or removed since the last save, this would not be reflected in UsedRange. That is why I used the End(xlDown).Row method.

The Intersect function here can be simplified to wsB.UsedRange.Columns("C")

And, I really can't see any purpose for the .Copy in that code. It is copying the entire contents of the used range in column C and pasting them in the second row of column B (assuming column B is all blank cells or all cells with data). But then it doesn't do anything with the copied data. It applies the filter to column C, then deletes all rows in the intersect between the UsedRange and Column C, visible or not.

[Whizbang]

I just thought of a problem. Column C is potentially going to contain blanks. That is why you are trying to delete them. But the code I supplied tries to find the last row in column C using the .End(xlDown) method. This will not work if there is a mix of cells with data and blank cells in column C. It needs to check a column that has contiguous data. So we would need to define the Key Column somehow. I had been testing using a criteria other than a blank value.

Please Login or Register  to view this content.

[Cyberpawz]

I just thought of a problem. Column C is potentially going to contain blanks. That is why you are trying to delete them. But the code I supplied tries to find the last row in column C using the .End(xlDown) method. This will not work if there is a mix of cells with data and blank cells in column C. It needs to check a column that has contiguous data. So we would need to define the Key Column somehow. I had been testing using a criteria other than a blank value.

Please Login or Register  to view this content.

Will this work under Option Explicit rules?

[Whizbang]

Sure. You just need to declare your variables is all. Option explicit simply forces you to delcare all variables. This helps prevent typos while coding, and therefore reduces bugs.

So...

Please Login or Register  to view this content.

[Cyberpawz]

Sorry, been dealing with the courts for the past day. Just one more question, the e.g means what in the formula?

[Whizbang]

That is just a comment. Everything past the ' will not be processed in any way. It is just a way to leave notes to anyone who might read the code in the future.

Here is a good article that talks about basic programming practices: http://www.geekgirls.com/vba_techniques.htm

[Cyberpawz]

That is just a comment. Everything past the ' will not be processed in any way. It is just a way to leave notes to anyone who might read the code in the future.

Here is a good article that talks about basic programming practices: http://www.geekgirls.com/vba_techniques.htm

The only reason I asked is because after looking at this:

Please Login or Register  to view this content.

It should be if I take out the comments:

Please Login or Register  to view this content.

Correct?

[Whizbang]

Try it and see. Or highlight the text below to reveal the answer.

Doing so will make your criteria behave as text. So, unless you have the characters "#>0" in your cells, the table will filter down to no rows. ">0" is the proper syntax to get cells that have a value greater than 0.

[Cyberpawz]

So how would I do it make it work as a number? The field I need deleted the way I have it will work, but I may need it to work as text one time in the future.

Also I noticed something...

Please Login or Register  to view this content.

The & "2:" part of it, what does that do?

[Whizbang]

To make it work as a number, simply use ">0". Excel will interpret that as "greater than 0". If you want to force it to look for the text string of ">0" then you would use "=>0" as your criteria.

For your second question:

StartColumn = "A"

The & concatenates two strings together to create a single string.

So,...

StartColumn & "2:"

...gives you...

"A2:"

Please Login or Register  to view this content.

This evaluates to "A2:A100", assuming you have 100 rows of data.

[Cyberpawz]

To make it work as a number, simply use ">0". Excel will interpret that as "greater than 0". If you want to force it to look for the text string of ">0" then you would use "=>0" as your criteria.

For your second question:

StartColumn = "A"

The & concatenates two strings together to create a single string.

So,...

StartColumn & "2:"

...gives you...

"A2:"

Please Login or Register  to view this content.

This evaluates to "A2:A100", assuming you have 100 rows of data.

So If I wanted to start it from the top, I'd take that out entirely? or how would that be written? The reason I'm asking is because where this is working at, all the rows need to be deleted including from the top down so that what is left is what I require to make this work flawlessly.

[Cyberpawz]

Ok, I've just tried running this for the first time, I see this line as red.

Please Login or Register  to view this content.

The Compiler error is:

Expected: list separator or )

this is the entire script.

Please Login or Register  to view this content.

The Search Criteria is meant to be empty.

[Cyberpawz]

Solved it...

Please Login or Register  to view this content.

is there was no space in

Please Login or Register  to view this content.

between the Column and &... I put it there, and it works now. thanks for the help.