Selectblank cells
 

Hi

I want to select blank cells programatically. I used the following code. I
actually tries two different range objects.

The problem is that, not all of the blank cells inside the used range get
selected. I clicked on some of the blank cells that did not get selected and
they were definitely blank. Does anyone know why some of the blank cells
don't get selected.

Thanks


Sub HighligteCells()

Dim rg As Range
Set rg = RealUsedRange

'ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlan ks).Select

rg.SpecialCells(xlCellTypeBlanks).Select


End Sub




Public Function RealUsedRange() As Range

Dim FirstRow As Long
Dim LastRow As Long
Dim FirstColumn As Integer
Dim LastColumn As Integer

On Error Resume Next

FirstRow = Cells.Find(What:="*", After:=Range("IV65536"),
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

FirstColumn = Cells.Find(What:="*", After:=Range("IV65536"),
LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow,
LastColumn))

On Error GoTo 0

End Function

Selectblank cells
 

On Aug 15, 10:06*am, DZ wrote:
Hi

I want to select blank *cells programatically. I used the following code. I
actually tries two different range objects.

The problem is that, not all of the blank cells inside the used range get
selected. I clicked on some of the blank cells that did not get selected and
they were definitely blank. Does anyone know why some of the blank cells
don't get selected.

Thanks

Sub HighligteCells()

Dim rg As Range
Set rg = RealUsedRange

'ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlan ks).Select

*rg.SpecialCells(xlCellTypeBlanks).Select

End Sub

Public Function RealUsedRange() As Range

* * Dim FirstRow * * * *As Long
* * Dim LastRow * * * * As Long
* * Dim FirstColumn * * As Integer
* * Dim LastColumn * * *As Integer

* * On Error Resume Next

* * FirstRow = Cells.Find(What:="*", After:=Range("IV65536"),
LookIn:=xlValues, LookAt:= _
* * xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

* * FirstColumn = Cells.Find(What:="*", After:=Range("IV65536"),
LookIn:=xlValues, LookAt:= _
* * xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

* * LastRow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues,
LookAt:= _
* * xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

* * LastColumn = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues,
LookAt:= _
* * xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

* * Set RealUsedRange = Range(Cells(FirstRow, FirstColumn), Cells(LastRow,
LastColumn))

* * On Error GoTo 0

End Function

Hi,

I have encountered this many times before but cannot remember how to
duplicate it, a workaround that works for me is to do a find and
replace, so find all "" cells and replace them with a character then
do the opposite something along the lines of...

ActiveSheet.UsedRange.Replace "", "^-^", xlWhole
ActiveSheet.UsedRange.Replace "^-^", "", xlWhole

James

Selectblank cells
 

Yes It worked

Thank you