0

I need to show a pop up message indicating that some information is missing in the table according to each column, e.g. if one cell is empty in column A then a pop up message will say 'please fill in the missing values in Column A', 'please fill in the missing values in Column B'...

Table with merged cells

LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

For Each Cell In Selection
For Row = 1 To LastRow
    If Cells(Row, 1).Value = "" Then
        if isempty(Cells(Row,1).mergearea().Value = true
            MsgBox "Please fill in the code"
    End If

    If Cells(Row, 2).Value = "" Then
        MsgBox "Please fill in the description" & Row
    End If

    If Cells(Row, 8).Value = "" Then
        MsgBox "Please fill in the discounting %" & Row

            ElseIf Cells(Row, 8).Value <> ">36%" Then
            Cells(Row, 8).Interior.ColorIndex = 4
    End If
Next Row

Should I used for each cell in selection or for next loop? This code will be used as a template which means the number or rows are different each time depending how many 'code' items (column A) is listed, does the first 3 lines of code suffice to find out the last row of not empty cells?

When I execute the code there's always more pop up messages than the actual number of empty cells. It seems like I can't identify whether a merged cell is empty or not.

Lastly, I also need to see the value in the discounted column whether the valued inside the cells > 36%, if yes then the whole row is highlighted. How can I code that? Thanks a lot guys!

kaylavigne
  • 1
  • 1
  • You don't need to `Select`. I suspect you don't need the `For Each cell in Selection` at all. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for more detail. – BigBen Jun 19 '19 at 02:00
  • Also, in regards to your comment that "there's always more pop up messages than the actual number of empty cells": That will be case if you're looping over each cell. As an example, if A2:A3 is a merged cell, then the message will be displayed for *both* A2 and A3. – BigBen Jun 19 '19 at 02:07
  • You can take some Ideas from this [Link](https://stackoverflow.com/a/9452164/5720144) .. The Answer actually detects the merged cells with Values like Constants and Formula. What you can do is skip those from the entire selection and others will be blank. So if blank is Yes then you can proceed with your code. – Mikku Jun 19 '19 at 03:04
  • Thanks BigBen and Mikku! Thanks for your tips! @Mikku but what is the table has non-merged cells that I also have to run through and detect whether its blank? – kaylavigne Jun 19 '19 at 03:25
  • @kaylavigne ... See that macro will tell you which cells are merged and have any value in them. Now the remaining cells are blank in your table. You can work out a logic using `Intersect` or anything else to loop through those blank cells. – Mikku Jun 19 '19 at 03:34
  • @Mikku i tried running the codes u suggested but how can I modify it to be blank instead of constant/formula? I'm a total beginner and really sorry for the silly question! Also, the codes only show an aggregate area of the merged cells e.g. if I merged A1:A3 and A4:A8, it actually considered A1:A8 alltogether, how can I distinguish them? It'd be clearer if u take a look at the link to the image! – kaylavigne Jun 19 '19 at 04:45

0 Answers0