 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008cells 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 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 > Hi > I was using following code as part of macro which worked fine but now it [quoted text clipped - 6 lines] > Thank you > Tina 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"). > Hi > I was using following code as part of macro which worked fine but now it [quoted text clipped - 6 lines] > Thank you > Tina 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 > Hi tina, I get mixed results, but if I use: > [quoted text clipped - 16 lines] > > Thank you > > Tina 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. > Hi > I tried both methods still not working . I know the cells are blank as the [quoted text clipped - 27 lines] > > > Thank you > > > Tina |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.