|
How do you refer to empty cells in coding?
If I want the cells with I: If cell = "I" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8 So How do I refer to Empty cells? If cell = *EMPTY* Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8 -- Turquoise_dax ------------------------------------------------------------------------ Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185 View this thread: http://www.excelforum.com/showthread...hreadid=554622 |
How do you refer to empty cells in coding?
I tend to use
if len(cell.value)=0 then CellIsEmpty But I'm sure it's not the only way!!!! Turquoise_dax wrote: If I want the cells with I: If cell = "I" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8 So How do I refer to Empty cells? If cell = *EMPTY* Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8 -- Turquoise_dax ------------------------------------------------------------------------ Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185 View this thread: http://www.excelforum.com/showthread...hreadid=554622 |
How do you refer to empty cells in coding?
Try ...
If cell = "" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8 |
How do you refer to empty cells in coding?
I should have put all of it... Dim cell As Range For Each cell In Range("B:B") If cell= *???* Then cell.Offset(0, 1).Interior.Pattern = xlPatternGray8 Next I tried "", not working... -- Turquoise_dax ------------------------------------------------------------------------ Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185 View this thread: http://www.excelforum.com/showthread...hreadid=554622 |
How do you refer to empty cells in coding?
If IsEmpty(cell.Value) Then
or If cell.Value = "" Then or If Len(cell.Value) = 0 Then -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Turquoise_dax" wrote in message news:Turquoise_dax.29t99r_1150991807.572@excelforu m-nospam.com... If I want the cells with I: If cell = "I" Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8 So How do I refer to Empty cells? If cell = *EMPTY* Then cell.Offset(0, 2).Interior.Pattern = xlPatternGray8 -- Turquoise_dax ------------------------------------------------------------------------ Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185 View this thread: http://www.excelforum.com/showthread...hreadid=554622 |
How do you refer to empty cells in coding?
On Thu, 22 Jun 2006 11:14:57 -0500, Turquoise_dax wrote:
I should have put all of it... Dim cell As Range For Each cell In Range("B:B") If cell= *???* Then cell.Offset(0, 1).Interior.Pattern = xlPatternGray8 Next I tried "", not working... Maybe you have some garbage in the cells, so they look as empty but are NOT empty. Try the Trim() function: If Trim(cell.value)= "" Then cell.Offset(0, 1).Interior.Pattern=xlPatternGray8 End If -- PL |
How do you refer to empty cells in coding?
I did n(cell.Value) = 0 Then cell.Offset(0, 1).Interior.Pattern = xlPatternGray8 It works, but the program is jamming for a long time, often I get the "not responding messge" If I get rid of this line, the code works just fine.... (Any idea why excel has trouble processing this one?) -- Turquoise_dax ------------------------------------------------------------------------ Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185 View this thread: http://www.excelforum.com/showthread...hreadid=554622 |
How do you refer to empty cells in coding?
Great, another problem came up.... Is there something I can add to my program so that Excel applies my conditionnal formatting (for empty cell) until the end of my file only (and not to the 65536th row....) -- Turquoise_dax ------------------------------------------------------------------------ Turquoise_dax's Profile: http://www.excelforum.com/member.php...o&userid=35185 View this thread: http://www.excelforum.com/showthread...hreadid=554622 |
How do you refer to empty cells in coding?
On Thu, 22 Jun 2006 11:41:02 -0500, Turquoise_dax wrote:
I did n(cell.Value) = 0 Then cell.Offset(0, 1).Interior.Pattern = xlPatternGray8 It works, but the program is jamming for a long time, often I get the "not responding messge" If I get rid of this line, the code works just fine.... (Any idea why excel has trouble processing this one?) Formatting multiple cells this way is very inefficient for large amountsd of data. If you want to get rid of "Not reponding" behaviour, execute DoEvents every 10 or 100 iterations. This won't speed up the process (in fact, it will slow down it). If you want to go REALLY fast, try to: - insert a column - put consecutive numbers to that column (to store the original order of rows) - sort table using the "conditional" column - format ALL the neccessary cells in ONE instruction (well, you'll have to find the first and the last cell in a block) - sort the table back to get the original order - remove the "sorting" column This works fine for me, I have really huge tables to format (more that 20k rows, more than 20 cols) and multiple autoformatting using this method takes less than a second. -- PL |
| All times are GMT +1. The time now is 06:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com