I have been using the following formula on one of my spreadsheets but I think it may be incorrect: If (Val(Cells(tmprow, 6)) = 0 Or IsNull(Cells(tmprow, 11))) And (Val(Cells(tmprow, 12)) = 0 Or IsNull(Cells(tmprow, 12))) Then
tmpsel = tmprow amp; ":" amp; tmprow Rows(tmpsel).Select Selection.Delete Shift:=xlUp
SO, if I am working in ROW 7 is this formula stating:
If the VALUE of G7 is 0 or K7 is Blank AND L7 = 0 or Blank then select this row for deletion?
I am trying to fix a problem on the spreadsheet but need to understand this formula first. The problem that has been brought to my attention is that if the value of G7 = RG545454 then the row is being deleted. It seems that the above formula does not like the ALPHA in the cheque number (G7 is a cheque number). If the cheque number in G7 is 545454 it is NOT Deleted?
Any help to assist me in understanding the formula or a suggestion to fix it would be GREATLY appreciated...
I think your code is OK except that G is the 7th column not the 6th.
Sorry for the confusion. Indeed my Cheque Number is located in Column F not G. This may be why nobody else answered...
Was the rest of my decipher correct in regards to the K and L statements?
Any idea why the row would be deleted if F7 is RG545454 but will remain if F7 is 545454?
THANKS Again Andrew,
Val won't recognise RG545454 as a number. It stops reading the string at the first character it can't recognize as part of a number.
THANKS Andrew. Any suggestions on revising the formula so it will recognize RG? I don't really need it to know it is a number I just want the row to remain if there is a Value in there.
Here is the formula that I have in F7:
=IF('Input Sheet'!$A17="ER",'Input Sheet'!E17,"")
SO, IF the answer to the formula is anything but "" (blank) I would like the row to remain.
If Len(Cells(tmprow, 6).Value) = 0 And (Val(Cells(tmprow, 12)) = 0 Or IsNull(Cells(tmprow, 12))) ThenTHANKS Andrew that seems to do the trick. One LAST question regarding the formula. If I was correct in stating that (tmprow, 12) represents Column L (L7 in my example) then I must have done something wrong because I have no data or formulas in that Column. What my formula should state is that:
If there is a value in G7 and H7 is > 0 then Keep that row. Is the formula below correct?
If Len(Cells(tmprow, 6).Value) = 0 And (Val(Cells(tmprow, 7)) = 0 Or IsNull(Cells(tmprow, 12))) Then
THANKS AGAIN for ALL your help Andrew,
Have a GREAT day,
To check that column G is empty and that column H contains a number greater than zero:
If Len(Cells(tmprow, 6).Value) = 0 And (IsNumeric(Cells(tmprow, 7).Value) And Val(Cells(tmprow, 7).Value)) = 0 Then
THANKS Andrew... As ALWAYS you have been MOST helpful.
Have an EXCELLENT day,