#1
|
|||
|
|||
Row Looks to be blank but its not
I have a workbook that I use to reformat data from its original format into the format I want.
In that workbook there is a macro that deletes all of the blank rows on the main sheet Code:
Sub delblankrows() Dim s1 As Worksheet Dim tmpR As Range Dim rowcount As Long, colcount As Long, i As Long, j As Long, k As Boolean 'Change "Sheet1" to the name of your worksheet. Set s1 = Sheets("sheet1") Set tmpR = s1.UsedRange rowcount = tmpR.Rows.Count colcount = tmpR.Columns.Count 'Starts from bottom row and looks for non-empty cells from left to right. 'Moves to row above if non-empty cell is found. 'If none is found, then deletes row and shifts values up. For i = rowcount To 1 Step -1 k = 0 For j = 1 To colcount If tmpR.Value2(i, j) <> "" Then k = 1 Exit For End If Next j When I select the row and "clear contents" and run the macro again the row gets deleted. I dont even know where to start figuring this one out.... |
#2
|
|||
|
|||
Update
After giving some more thought to this problem I think this might be relevant.
There are other macros that I run to delete unwanted data. A sample is Code:
Sub DELData() ' ' DELData Macro ' ' Sheets("Sheet1").Select Application.CutCopyMode = False Selection.Copy Cells.Replace What:="Sample Text", Replacement:="", LookAt _ :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Sheets("Control").Select End Sub |
#3
|
||||
|
||||
I can't give you a complete answer on this, bremen22; I have yet to work out a complete theory of the contents of a cell. But I can agree with your observation; cells that I change from something to "" are not quite the same as cells that never had anything in them in the first place. I'm pretty sure I have sometimes done an <End>,<Down> in a column and have it stop at a cell that used to have something in it and now doesn't.
I'm not sure, but I've a notion that hitting <Del> clears that status. If I'm right, that implies that an empty string is still data ... which from a programmer's point of view makes sense, actually. So my guess is that a cell with "" in it is not yet Empty, but after I hit <Del>, or in your case after you ClearContents, then it's Empty. All this makes sounds so obvious now that I'm writing it down, but I've never sat down to think about it carefully before. I guess I'll have to experiment a bit. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
won't show blank cell | bhum | Excel | 2 | 01-28-2014 02:34 AM |
Document comes up as a blank | Grover | Word | 1 | 11-29-2013 07:24 PM |
Page numberring 1 - blank - 2 - blank etc | Intern | PowerPoint | 0 | 09-02-2011 01:16 AM |
hidden blank pages | mljm | Word | 3 | 06-19-2009 03:18 AM |
How to turn all blank lines into non-blank for legal forms | sieler | Word | 3 | 04-12-2009 01:38 PM |