is there any quick way to detect all empty rows and columns in the selected
data without reading all cell values?? quick help would be appreaciated.

thanks.

AFAIK there's no way without looping through the cells and checking
them.
sorry!
susan


On Aug 1, 1:41*pm, guest <gu...@discussions.microsoft.com> wrote:
> is there any quick way to detect all empty rows and columns in the selected
> data without reading all cell values?? quick help would be appreaciated.
>
> thanks.

Sub Color_Blanks()
Dim rng As Range
Set rng = Nothing
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
'do nothing
Else
rng.Interior.Color = vbYellow
End If
End Sub


Gord Dibben MS Excel MVP

On Fri, 1 Aug 2008 10:41:02 -0700, guest <(E-Mail Removed)>
wrote:

>is there any quick way to detect all empty rows and columns in the selected
>data without reading all cell values?? quick help would be appreaciated.
>
>thanks.

A cell with a formula in it would not be empty
Delete ALL rows that are Entirely Blank (#RemoveEmptyRows)
http://www.mvps.org/dmcritchie/excel...emoveEmptyRows
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm



"Gord Dibben" <gorddibbATshawDOTca> wrote in message news:(E-Mail Removed)...
> Sub Color_Blanks()
> Dim rng As Range
> Set rng = Nothing
> On Error Resume Next
> Set rng = Selection.SpecialCells(xlCellTypeBlanks)
> On Error GoTo 0
> If rng Is Nothing Then
> 'do nothing
> Else
> rng.Interior.Color = vbYellow
> End If
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Fri, 1 Aug 2008 10:41:02 -0700, guest <(E-Mail Removed)>
> wrote:
>
>>is there any quick way to detect all empty rows and columns in the selected
>>data without reading all cell values?? quick help would be appreaciated.
>>
>>thanks.
>

Sorry misread as delete, ignore my reply. Gord had it right.
--
David


"David McRitchie" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> A cell with a formula in it would not be empty
> Delete ALL rows that are Entirely Blank (#RemoveEmptyRows)
> http://www.mvps.org/dmcritchie/excel...emoveEmptyRows
> --
> HTH,
> David McRitchie, Microsoft MVP -- Excel
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
>
>
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message news:(E-Mail Removed)...
>> Sub Color_Blanks()
>> Dim rng As Range
>> Set rng = Nothing
>> On Error Resume Next
>> Set rng = Selection.SpecialCells(xlCellTypeBlanks)
>> On Error GoTo 0
>> If rng Is Nothing Then
>> 'do nothing
>> Else
>> rng.Interior.Color = vbYellow
>> End If
>> End Sub
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Fri, 1 Aug 2008 10:41:02 -0700, guest <(E-Mail Removed)>
>> wrote:
>>
>>>is there any quick way to detect all empty rows and columns in the selected
>>>data without reading all cell values?? quick help would be appreaciated.
>>>
>>>thanks.
>>