Convert "" to real blank cell

[Guest]

Hello, I want to use the benefit of End xldown or UP on moving the cursor,
but It does not work if the cell is not really blank/no content at all. My if
statement produce " " in the cell which is not blank although it appreas
blank, So, I can not use my End xl down or up.

Can anybody help me how can I change it?, my formula is like this:
=IF(AG7=AG8,"","Ini").

Thanks in advance

Frank

 

[Guest]

The cell is no longer blank after you have inserted a formula. It contains
the formula irrespective of the result of the formula.

Regards,

OssieMac

 

[Guest]

Ossie, are you saying if I copy it to value it will show a real blank?,
because what I wanted to ck is instead of manual ck, I want to use formula to
ck, and i can easily move to the next anomaly by pressing button End then
down, all the blank will be passed and to next unblank cell in the same
column.

I appreciate if you have more idea.

Thanks
Frank

 

[Guest]

As Ossie wrote, once a cell contains something (a formula, a number, a text,
etc.) it is not empty. For your situation, I would create a short macro that
would populate a new column based on the result of your formula.

In this example, the value to test is 1 and it is in the first column, it
populates the 9th column and does it for the first 100 rows of the current
sheet. Test it first on a blank sheet and put 1 randomly in column A, then
run the macro. Later, adapt to your needs:

Sub CreateStopMarks()

For i = 1 To 100

If Cells(i, 1) = 1 Then
Cells(i, 9) = "Stop"
Else
Cells(i, 9) = ""
End If

Next i


End Sub

 

[Guest]

Hi Frank,

Have a look at the following example and see if it gives you a suggestion as
to how you might overcome your problem. It uses xlUp and stops on any cell
that is not empty. It trims all blanks from the value of the activecell and
tests it's length. If it is zero length then it loops again from the
activecell until it comes to a cell with real data.

Note: It uses xlUp from the active cell in each loop. It cannot restart from
the original last cell. It can be done without actually selecting the cells
but the example lets you see what is happening so you can understand the
logic.

Sheets("Sheet1").Select

'Select last cell in column 1
Cells(Rows.Count, 1).Select

'Use xlUP to select non blank cell
Do
ActiveCell.End(xlUp).Select
MsgBox "Activecell address = " & ActiveCell.Address
'Test length of data in active cell. If zero then loop
Loop While Len(Trim(ActiveCell)) = 0

Regards,

OssieMac

 

[Guest]

Frank,

Modify the loop line to this. If you had a column with no real data then it
would go into an eternal loop the way I had it. This line will ensure it
stops at row 1 if no real data found.


Loop While Len(Trim(ActiveCell)) = 0 And ActiveCell.Row <> 1

Regards,

OssieMac

 

[Guest]

Thanks to all of you, your ideas enrich my knowledge, I am not expert in
macro and VBA, because my specialty in fact is Finance & Accounting. After
reading on other postings in this forum, I got the easier way by datasorting
the column and I delete all the "" result, then I resorted back as the
original, than I got what I need.

Thanks,

Frank