Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cells not registering as blank
Hi
I was using following code as part of macro which worked fine but now it errors as no blank cells I tried highlighting row 2 and selecting edit goto special blank cells states no cells . there are empty cells Rows("2:2").Select Selection.SpecialCells(xlBlanks).Select I have not made changes to spreadsheet layout just imported new data any Ideas Thank you Tina |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cells not registering as blank
Hi Tina,
Sometimes when importing data the imported cells look blank, but really contain spaces that you cannot see. For example, in a cell, press space bar a couple times and press enter. The cell looks empty, but it is not. It contains some spaces. You may have to resort to a macro to "clean" up your sheet if this is the case. Look at the "Trim" function in help. HTH "tina" wrote: Hi I was using following code as part of macro which worked fine but now it errors as no blank cells I tried highlighting row 2 and selecting edit goto special blank cells states no cells . there are empty cells Rows("2:2").Select Selection.SpecialCells(xlBlanks).Select I have not made changes to spreadsheet layout just imported new data any Ideas Thank you Tina |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cells not registering as blank
Hi tina, I get mixed results, but if I use:
Range("B2:H2").SpecialCells(xlBlanks).Select Then I get the right results for the blank cells. If I use Rows(2).Select, I get that row selected, but when I use Rows("2:2").SpecialCells(xlBlanks).Select, it gives me mixed results. Could be it don't like the Rows("2:2"). "tina" wrote: Hi I was using following code as part of macro which worked fine but now it errors as no blank cells I tried highlighting row 2 and selecting edit goto special blank cells states no cells . there are empty cells Rows("2:2").Select Selection.SpecialCells(xlBlanks).Select I have not made changes to spreadsheet layout just imported new data any Ideas Thank you Tina |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cells not registering as blank
Hi
I tried both methods still not working . I know the cells are blank as the data imported is my data and i tried coping columns with data to new sheet i.e. columns a:da then tried still failed to make it even more confusing the formulas now works in first instance and allows me to enter data in first cell found but when repeat later to get next cell it fails I can get round this by range naming cell but I am confused why happening as this macro has been working fine for a couple of years Thanks for you time Tina "JLGWhiz" wrote: Hi tina, I get mixed results, but if I use: Range("B2:H2").SpecialCells(xlBlanks).Select Then I get the right results for the blank cells. If I use Rows(2).Select, I get that row selected, but when I use Rows("2:2").SpecialCells(xlBlanks).Select, it gives me mixed results. Could be it don't like the Rows("2:2"). "tina" wrote: Hi I was using following code as part of macro which worked fine but now it errors as no blank cells I tried highlighting row 2 and selecting edit goto special blank cells states no cells . there are empty cells Rows("2:2").Select Selection.SpecialCells(xlBlanks).Select I have not made changes to spreadsheet layout just imported new data any Ideas Thank you Tina |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cells not registering as blank
This should work.
Sub blnkSpec() lstCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column Range("A2", Cells(2, lstCol)).SpecialCells(xlCellTypeBlanks).Select End Sub It seems that when the Rows collection is used, it somehow tries to incorporate the used range. I don't know why this occurs, but it did it several times with me, and sometimes it ignored the specical cells altogether and did nothing. But the code above worked every time. It sets a variable range on row 2 that runs from A2 to cell in the last column with data on that row. I think that is what you really wanted instead of the entire row's blank cells. "tina" wrote: Hi I tried both methods still not working . I know the cells are blank as the data imported is my data and i tried coping columns with data to new sheet i.e. columns a:da then tried still failed to make it even more confusing the formulas now works in first instance and allows me to enter data in first cell found but when repeat later to get next cell it fails I can get round this by range naming cell but I am confused why happening as this macro has been working fine for a couple of years Thanks for you time Tina "JLGWhiz" wrote: Hi tina, I get mixed results, but if I use: Range("B2:H2").SpecialCells(xlBlanks).Select Then I get the right results for the blank cells. If I use Rows(2).Select, I get that row selected, but when I use Rows("2:2").SpecialCells(xlBlanks).Select, it gives me mixed results. Could be it don't like the Rows("2:2"). "tina" wrote: Hi I was using following code as part of macro which worked fine but now it errors as no blank cells I tried highlighting row 2 and selecting edit goto special blank cells states no cells . there are empty cells Rows("2:2").Select Selection.SpecialCells(xlBlanks).Select I have not made changes to spreadsheet layout just imported new data any Ideas Thank you Tina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
Registering UDFs | Excel Programming | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Code for Registering Add-in | Excel Programming | |||
Registering an OCX with VBA | Excel Programming |