find empty cells till the last row with vba

[avishain]

hello there,

got a range from column a to w.

the numbers of rows vary from time time.

the user must fill manually data in colomns o,v,w.

i need help with the following:

1.i need a vba code that finds the last non-empty cell in column A

2.runing a loop from cell A2 to the last cell in column A

3.find if there are empty cells in columns o,v,w

4.if there's empty cell - is there a way to show it with a msg box? (even if it finds 100 cells) or option no.2 is to color these cells

i want that the user will see exactly where are the empty cells.

thank you very much

 

[evnoort]

I use this function to find the last cell.

Private Function FindLastCell() As Integer
' Fetches last cell in active worksheet
Dim LastColumn As Integer
Dim lastRow As Long
Dim LastCell As range

If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
lastRow = Cells.find(what:="*", after:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).row
'Search for any entry, by searching backwards by Columns.
'LastColumn = Cells.Find(What:="*", After:=[A1], _
'SearchOrder:=xlByColumns, _
'SearchDirection:=xlPrevious).Column
'MsgBox Cells(LastRow, LastColumn).Address
End If
FindLastCell = lastRow
End Function

 

[Marcelo Branco]

Hi,

You can highlight the empty cells using Conditional Formatting (no need of VBA)

For example
Select the entire column O
Home > Conditional Formatting > New Rule > Use aformula to...

enter this formula
=AND(A1<>'"",O1="")

Format button
Fill and pick a color
Ok, Ok

Do the same in columns V and W

HTH

M.

 

[avishain]

Hi,

You can highlight the empty cells using Conditional Formatting (no need of VBA)

For example
Select the entire column O
Home > Conditional Formatting > New Rule > Use aformula to...

enter this formula
=AND(A1<>'"",O1="")

Format button
Fill and pick a color
Ok, Ok

Do the same in columns V and W

HTH

M.

thanks but i cant trust the user to correct himself...i want that the VBA will prevent him from procceed once there's empty cell.

 

[Weaver]

Try the following:

Sub test()
    Dim lr, m
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    On Error Resume Next
    Set m = Intersect(Range("1:" & lr), Range("o:o,v:w")).SpecialCells(xlCellTypeBlanks)
    If Not m Is Nothing Then
        m.Interior.ColorIndex = 3
        MsgBox "Empty cells highlighted"
    End If
End Sub

 

[avishain]

Try the following:

Sub test()
    Dim lr, m
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    On Error Resume Next
    Set m = Intersect(Range("1:" & lr), Range("o:o,v:w")).SpecialCells(xlCellTypeBlanks)
    If Not m Is Nothing Then
        m.Interior.ColorIndex = 3
        MsgBox "Empty cells highlighted"
    End If
End Sub

thanks u very much!! i really appricate you're help.

 

[avishain]

Try the following:

Sub test()
    Dim lr, m
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    On Error Resume Next
    Set m = Intersect(Range("1:" & lr), Range("o:o,v:w")).SpecialCells(xlCellTypeBlanks)
    If Not m Is Nothing Then
        m.Interior.ColorIndex = 3
        MsgBox "Empty cells highlighted"
    End If
End Sub

got a little problem with the code.

if everything is ok and there arn't any blank cell - i want that my original macro will procceed....so i tried to edit the code but cant figure out what's the problem...:


Sub help()
Dim lr, m


lr = Cells(Rows.Count, "A").End(xlUp).Row
On Error Resume Next
Set m = Intersect(Range("1:" & lr), Range("o,v:w")).SpecialCells(xlCellTypeBlanks)
If Not m Is Nothing Then
m.Interior.ColorIndex = 3
MsgBox "the cells are red "

Exit Sub
Else

"my original code"

end if

end sub.

what's wrong?

 

[Weaver]

Apologies

After the dim statement, add the line

set m = nothing

Which should initialise the variable so that the check happens correctly later in the code.

 

[avishain]

works perfect now.


thank you again.