Please note that you can also use the "inspect document" feature in Excel to see if a document has hidden sheets/rows/columns. RCount & " hidden rows found." & vbLf & _ MsgBox sCount & " hidden sheets found." & vbLf & _ If rng.Hidden = True Then cCount = cCount + 1 If rng.Hidden = True Then rCount = rCount + 1įor Each rng In wks.Columns ' or If wks.Visible = xlSheetVeryHidden Then sCount = sCount + 1įor Each rng In wks.Rows ' or If wks.Visible = xlSheetHidden Then sCount = sCount + 1
Note that using the 'UsedRange' for rows/columns means that the count will not include rows/columns that do not contain any data (but the macro will be faster as a result).ĭim sCount As Long, rCount As Long, cCount As Long. SText = aWF.Here is a method, very similar to Banjoe's, that will return the count of how many hidden sheets, columns, and rows there are (assuming you don't need the info on which rows and just want a report). SText = aWF.Substitute(sText, Chr(J), sSubText) If you try the macro and you get an error on one of the lines that use the Replace function, then use this version of the ReplaceClean macro instead:įunction ReplaceClean2(sText As String, Optional sSubText As String = " ") This VBA function is not available in all the versions of VBA used with the different versions of Excel. If you look back at the ReplaceClean1 macro presented earlier, you see that it uses the Replace function. The following usage simply removes the non-printing characters, the same as the CLEAN function: The following example replaces the non-printing characters with a dash: If you want the characters replaced with something else, just provide the text to replace with. In this case, all non-printing characters in cell B14 are replaced with a space. You use this function in the following manner within your worksheet: SText = Replace(sText, vAddText(J), sSubText) Consider the following example macro:įunction ReplaceClean1(sText As String, Optional sSubText As String = " ") It isn't too difficult to create your own version of the CLEAN worksheet function that, instead of simply removing non-printing characters, replaces them with spaces. You can, of course, use a macro to get rid of the offending characters. Some people report that they get exactly the results they want by using this round-trip approach to working with the data. You can then paste the data back into Excel. 
Copy the data from Excel to a Word document (paste it as regular text), and then replace the offending characters. One thing to try is to use Word in your "clean up" operations. If it doesn't work, then you should try a different approach. If it does work, then you have learned a valuable technique for getting rid of the bad characters. This approach may or may not work, depending mostly on Excel and whether it let you accurately copy the offending character in step 1. If you want to replace the characters with spaces, put a single space in the Replace With box. If you want to just delete the characters, make sure there is nothing in the Replace With box.If nothing is still pasted, then you won't be able to use Find and Replace to get rid of the non-printing characters, and you can skip the rest of these steps. If nothing was pasted in step 3, then close the dialog box and try the steps again.
The character will most likely not look like the small box you selected and copied in step 1. This pastes the contents of the Clipboard (the offending character) into the Find What box.
With the insertion point in the Find What box, press Ctrl+V. The Replace tab of the Find and Replace dialog box. Excel displays the Replace tab of the Find and Replace dialog box. Choose Replace from the Edit menu or press Ctrl+H. This copies the character to the Clipboard. Within the cell that contains one of the small boxes, highlight the box and press Ctrl+C. To get rid of these characters you an try to use the Find and Replace feature of Excel. Excel displays the small boxes so that you know the character is there, even though it cannot be displayed or printed. In some cells you may notice that there are small boxes. 
For instance, you may have a comma-delimited text file generated by your company's accounting software, and you load the file into Excel.
FINDING HIDDEN CHARACTERS IN EXCEL HOW TO
If you work with files that originate from a non-Excel source, you can sometimes end up with characters in your cells that Excel doesn't know how to display properly.
0 kommentar(er)
