Range selection and cell data deletion problems in Excel 2010

[RBLampert]

I'm having two problems with an Excel 2010 macro. I run the macro once a month, each time on a new set of data that contains a varying number of rows. It is supposed to remove a reference number from one cell in each row. (All of these cells are in the same column.) Unfortunately, rather than working only on the cells having data, it works on a fixed range, which usually means it tries to "fix" many blank cells in that column, which means I have to clean out a lot of #VALUE! error messages after the macro runs. I want the macro to only work on the cells in that column that have data, no matter how many or how few rows there are.

Second, the reference number the macro is set up to remove is either 8 or 9 digits long, with a # sign before it and parentheses on either side: (#12345678) or (#123456789). There is text in front of this number that I need to keep. The macro works fine for removing the 9-digit numbers but leaves the space that separates the last letter of the text from the opening parenthesis of the 8-digit numbers. Leaving those spaces means I have to do additional work to clean them out later, which I'd like to avoid.

Here's the current code:

Sub Remove_Module_Numbers()
'
' Remove_Module_Numbers Macro
' This macro removes the Brainshark-assigned 9-digit module numbers.
'

'
Range("B1").Select
Selection.EntireColumn.Insert
Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=MID(C[-1],1,FIND("" ("",C[-1],1)-1)"
Range("B2").Select
Selection.AutoFill Destination:=Range("b2:b379")
Range(Selection, Selection.End(xlDown)).Select
Range("B2").Select
' Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
Range("B2").Select
Selection.EntireColumn.Delete

End Sub

I'm sure the fixes are relatively straight-forward but my VBA coding skills are limited and I haven't been able to figure out what to do. Any help would be greatly appreciated.

 

[SkipVought]

Hi,

When you go to the doctor, he/she asks you what your problem is. Often it is not the problem you think it is but an underlying issue. The doctor asks additional questions that may uncover this issue.

In this case you supplied some additional diagnostic information that doctor SkipVought saw stick out like a sore thumb.

it tries to "fix" many blank cells in that column, which means I have to clean out a lot of #VALUE!

I'd surmise that if you were to simply DELETE these rows containing empty cells and #VALUE cells, and convert your table to a Structured Table, which solves the #VALUE issue by automatically propagating formulas to new table rows, your problem might cease to exist.

Insert > Tables > Table...

It also does not appear that your table has a heading row.

Anyhow, if doing the Structured Table thing does not produce the desired results, please post back and also upload your workbook. That would help immensely.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RBLampert]

Thank you," Dr. Vought. OK, the cells containing the #VALUE! error all fall in rows AFTER the last row of data. Of course I can (and currently do) delete all of those rows but that's extra work I don't want to do.

I do need to propagate formulas in this analysis project later, and at that time I do create the structured table, but that process is irrelevant to these particular problems.

Also, the workbook does have a header row.

A version of the file in question is now attached. The cells I'm having trouble with are in Column A.

 

[SkipVought]

I fail to find any cells containing #VALUE??? Neither is the table a Structured Table???

You ought NEVER have those cells "after the last row of data" although you have some sort of needless desire to have unnecessary and irksome data like this.

So do you simply want to get rid of the stuff in the right of cells in columns A like [highlight #FCE94F](#926276093)[/highlight]?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RBLampert]

The #VALUE! error messages appear in Column A AFTER the macro runs, from the first blank row to row 379. If the macro runs only in rows that have data (which will always be consecutive), this problem will be solved.

And yes, the example you highlighted is what I want to get rid of. The macro currently does that successfully for 9-digit numbers but leaves a space after the last letter of the text that precedes the leading parenthesis when the number is 8 digits long. That becomes a problem later in the analysis, requiring me to go back to those cells in Column A, find the ones that have the trailing space, and delete it in each case. Again, not a difficult task (I can fix it in one cell and then copy that cell's contents to all that have the same text) but one I don't want to have to do if I can avoid it.

 

[SkipVought]

Again, the workbook you uploaded does not reflect these issues, neither does it have the macro you have been using. Furthermore, the UsedRange property of the sheet indicates data through row 32, so I can't reproduce this error. You must have "data" at row 379! Check to determine if there's something like a SPACE or other character in A379.

Nonetheless, simply use the Text to Columns feature, using DELIMITED on [highlight #FCE94F]([/highlight] ... and DO NOT IMPORT the second column (containing the number and trailing parenthesis.

You can record this to create a macro and then just add a loop to remove the trailing SPACE.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RBLampert]

OK, let's try this again from the top. First, the macro I sent you is one of many I have, all of which are saved in my personal.xlsb file. I access all of the macros I use through that file. That's why you don't see this macro embedded in the file. I get a file like the one I sent you every month, and perform a lot of data manipulation and analysis on it. I want to use macros, and do use macros, to speed up that process. This is one of the macros that does not work as I want it to (a) because it operates on rows that contain no data, and (b) because it does not always operate correctly on the cells that do have data.

Second, the #VALUE! error appears ONLY after the macro runs because, as you should have noticed, this line of code

Selection.AutoFill Destination:=Range("b2:b379")

tells it to autofill the changes the code a few lines above created in cells B2 through B379. Since, in this case, the data ends on row 32, the MID code produces the #VALUE! errors from rows 33 to 379. THERE IS NOTHING IN ANY OF THE CELLS FROM A33 (or whatever the last row of data is) TO B379 BEFORE THE MACRO RUNS.

I want the macro to find the last row of data (which is different every month) and stop executing this code

ActiveCell.FormulaR1C1 = "=MID(C[-1],1,FIND("" ("",C[-1],1)-1)"

(or whatever should replace it) at that last row of data, NOT continue to row 379.

Third, I receive this file as a .xlsx file, not as a text file, so I don't see how using a text-to-columns feature is going to help. The contents of Column A are as you see them in the file when it arrives.

Fourth, I don't understand why the MID function works correctly with 9-digit numbers but not 8, and ultimately, it doesn't matter. I just want the macro to do what I need it to do: for only the rows containing data, remove the space after the last text letter and the number, # sign, and parentheses from each cell in Column A, irrespective of how long that number is.

I hope that's clear.

 

[SkipVought]

Try this....

Selection.AutoFill Destination:=Intersect(Range([B2], [B2].End(xlDown)), ActiveSheet.UsedRange)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RBLampert]

Success! Now, how about that annoying extra space? It's still left if the module number had 8 digits.

 

[SkipVought]

Again, your procedure would be a lot shorter, quicker using, "the Text to Columns feature, using DELIMITED on ( ... and DO NOT IMPORT the second column (containing the number and trailing parenthesis)." It's all done in column A. No inserting, copy, formula, etc.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

BTW, in your example workbook, you have 13 instances of 2 SPACES followed by a PARENTHESIS " (", while the remainder have only one SPACE followed by a PARENTHESIS.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

okay, here's a formula that accommodates either one or two SPACES before the open PARENTHESIS.
[tt]
B2: =LEFT(A2,IF(ISERR(FIND(" (",A2)),FIND(" (#",A2),FIND(" (",A2))-1)
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RBLampert]

TWO spaces! OK, that explains why the MID function was working properly for the 9-digit numbers but not the 8-digit ones.

I'm still confused about the Text to Columns feature because the file I receive is NEVER a text file. I get it as an Excel file. John Walkenbach's book on VBA programming in Excel 2010 has only a one-page discussion of the Text to Columns feature, and it includes a macro to clear all of the delimiters for a single cell. That doesn't seem like it addresses my problem.

As I see it, there are two options. (1) Ask the lady who sends me the file each month to check with Brainshark, the web site that generates the file in the first place, to see if they can ensure only one space is inserted between the video name and the video number. If they can, the problem goes away. Or, (2) after the macro deletes the video number, have it check the last character of each cell in that column, and if that character is a space, delete it.

 

[RBLampert]

Your suggested formula for dealing with the two spaces came in as I was writing the comment above. I'll give the formula a try tomorrow.

Thanks.

 

[SkipVought]

JW's books are at the top of my go-to list!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[RBLampert]

Good to know! It's certainly not a "for Dummies" book!

BTW, I'm going to hold off on testing your other code for a few days. I've sent an e-mail to the lady I work with to see if there's something she can do at her end that will keep that extra space from being inserted in the first place. I think there is, but in case she can't fix it at her end, I have your code to use instead.

Thanks again.