Selectblank cells

  • Thread starter Thread starter DZ
  • Start date Start date
D

DZ

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(xlCellTypeBlanks).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 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(xlCellTypeBlanks).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
Click to expand...

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
 
Back
Top