need Macro to delete entire rows that are blank in col B only

[skyport]

need Macro to delete entire rows that are blank in col B only but leave all other rows untouched.

can someone please help me with this?

 

[hiker95]

need Macro to delete entire rows that are blank in col B only but leave all other rows untouched.

can someone please help me with this?

skyport,

So that we can get it right on the first try, can you supply a screenshot of the raw data in columns A, B, and, C?

And, can you supply a screenshot of what the results should look like?


See reply #2 at the next link, if you want to show small screenshots (per the above), of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[skyport]

thanks for sticking with me on this one. the last code gives me a runtime error 1004

 

[skyport]

Hi Hiker. just picture column B with some cells containing addresses and other cells have nothing. the idea is to eliminate all the rows that show blank in column B.

 

[hiker95]

thanks for sticking with me on this one. the last code gives me a runtime error 1004

skyport,

In your other post/thread you did ask me to look at this post/thread.

In order for me to continue I would like to see some screenshots per my reply #11, in this post/thread.

 

[DushiPunda]

thanks for sticking with me on this one. the last code gives me a runtime error 1004

skyport, have you tried my code from my post here, including making the changes I said you need to make: https://www.mrexcel.com/forum/excel...entire-rows-blank-col-b-only.html#post4856824

 

[skyport]

regarding post #9 I am using sheet 1 and row 1 is the beginning and I changed the X factor to 1000

regarding post #10 this was the last code I tried and got the 1004 runtime error

 

[hiker95]

skyport,

If I understand you correctly, then here is a macro solution for you to consider.

Sample raw data:

Excel 2007
	B
1	Title B
2	address 1
3
4	address 21
5
6	address 33
7	address 100
8
9	address 200
10
Sheet1

And, after the macro:

Excel 2007
	B
1	Title B
2	address 1
3	address 21
4	address 33
5	address 100
6	address 200
7
8
9
10
Sheet1

Sub skyport_ME1011896()
' hiker95, 06/28/2017, ME1011896
On Error Resume Next
Range("B1", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[DushiPunda]

regarding post #9 I am using sheet 1 and row 1 is the beginning and I changed the X factor to 1000

regarding post #10 this was the last code I tried and got the 1004 runtime error

skyport, I just tried the code from post #10 and received the same error. Specifically: "Cannot use that command on overlapping selections"

Regarding post #9 and, by proxy, post #2, I just tried it on my end and it worked perfectly. It did take a few moments to complete because it's looping 1,000 times, but it did work.

You've yet to say the results of my code - did it work? I'm assuming no. Did you get an error? If so, what error? Please copy and paste the entire macro you are running.

Edit: Just tried the code posted by hiker95 here, and it's working perfectly as well and is much more efficient than the code I've provided. Recommend using this solution. I think it's worth noting - as is, this code will run on whatever the active worksheet is when the macro is initiated. So if, for example, you have a command button on a separate sheet that you want to press, you either need to qualify the ranges, or you need to add a line to activate the correct sheet prior to the delete line. Hopefully that makes sense.

Also, hiker95's code is remarkably similar to the code posted by JLGWhiz in post #10, but there are a few diffferences. I wonder what exactly is causing JLGWhiz's code to fail.

 

[skyport]

I want to thank each of you once again for trying to help me. I love this forum because it not only helps with solutions but helps those providing the solutions to learn more and be even better. For this reason I like to try to give as much feedback as I can.


DushiPunda - as I mentioned earlier on your code I did not receive any specific error message but nothing just seemed to result from it and I have no idea why because as I mentioned earlier I pasted the code exactly in the module and the way you gave it to me.


Regarding Hiker, he and I go back years as he gave a wonderful solution to one of the first problems I posted on this form and as done again and again time after time with always great results in the end. However, for some reason I can not get this one to work either. One thing for sure, by his visual illustrations in post number 17, he clearly and totally sees exactly what we are trying to accomplish. I will say this, that I am not able to control other random data that may exist in other cells which may tend to exist and is also why I stated earlier that we needed to make the function based simply around column B although I cannot imagine if we properly do that, random data in other cells would have any effect in stopping this from functioning. Examples of the other types of data really can't be illustrated because it may always be different and should not be relative if the function of the code is based only on whether there is data in the cells of column B or not. Then maybe that's an error in my thinking but I don't know how to get around that. Once again, the concept that Hiker illustrated is exactly what the result should be based on the example of the data he showed. What he showed was very simply that if there was no data in a particular cell within column B, it would eliminate that entire row. That is precisely the goal. It is obviously working for two of you but not for me and I don't understand that I am sorry to say.

 

[vcoolio]

Hello Skyport,

Here's another take on Hiker's code:-

Sub ClearThem()

Columns(2).SpecialCells(4).EntireRow.Delete

End Sub

Another method would be to use Autofilter:-

Sub DeleteIt()
    With Sheet1.[A1].CurrentRegion
        .AutoFilter 2, ""
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

You may have to change "Sheet1" to "ActiveSheet".

But, as you say, its not clear why any of the previously mentioned codes are not working.

Cheerio,
vcoolio.