Need help please deleting rows under certain criteria

[Henner]

Hi,

Could someone please provide me with some macro code to add to an existing macro so that it deletes a row when the row immediately beneath it is blank, but only when the cell in column A above the blank row contains data.
For example, if cell A2 on row 2 contains data but row 3 is blank, then delete row 2.

After the macro has done this, could you please provide code to run through the sheet to bold the contents of a cell in Column A but only when the adjacent cell in column B is blank.
For example, if cell B5 is blank and cell A5 contains data, then bold the contents of cell A5.

Thanks,
Paul

 

[FryerTuck]

The following goes through rows 2 thru 10 to check for all blank in columns 1 thru 6. if so, it checks the previous row for data in columns 2 thru 6. If they are blank but cell A is not, it deletes that row, and resets so that the previous row can be checked for the criteria to delete. After the full deletion, there is the bolding check.

(This was not completely tested)

Sub Macro1()
Dim CntrR, CntrC, TTL As Integer
For CntrR = 2 To 10
TTL = 0
For CntrC = 1 To 6
TTL = TTL + VarType(Cells(CntrR, CntrC))
Next
If TTL = 0 Then
For CntrC = 2 To 6
TTL = TTL + VarType(Cells(CntrR - 1, CntrC))
Next
If Not (IsNull(Range("A" & CntrR - 1))) And TTL > 0 Then
Rows(CntrR - 1).Delete
CntrR = CntrR - 1
End If
End If
Next
For CntrR = 1 To 10
If Not (IsNull(Range("A" & CntrR))) And IsNull(Range("A" & CntrR)) Then
Range("A" & CntrR).Font.Bold = True
End If
Next
End Sub

 

[Henner]

Hi

Many thanks for taking the trouble to try and help me by providing the code. Unfortunately, when I run it, nothing changes - leaves in rows that need to be deleted and doesn't bold the cells in Column A that need to be bold.

If it will help, I can insert a sample of the worksheet here. However, I haven't done this before so if you can tell me how to do it, I will.

Regards,
Mark [/code]

 

[FryerTuck]

Rechecked the code. One error caused it not ot delete, and there could be a problem if you delete data in some cells. Try this variation instead:

Sub Macro1()
Dim CntrR, CntrC, TTL As Integer
For CntrR = 2 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
TTL = 0
For CntrC = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
TTL = TTL + Len(Cells(CntrR, CntrC))
Next
If TTL = 0 Then
For CntrC = 2 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
TTL = TTL + Len(Cells(CntrR - 1, CntrC))
Next
If Len(Range("A" & CntrR - 1)) > 0 And TTL = 0 Then
Rows(CntrR - 1).Delete
CntrR = CntrR - 2
End If
End If
Next
For CntrR = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
If Len(Range("A" & CntrR)) > 0 And Len(Range("B" & CntrR)) = 0 Then
Range("A" & CntrR).Font.Bold = True
End If
Next
End Sub


I did test this. It seems to do what you asked. It goes as far as the row beyond the last row that was entered and checks the columns as far as the right-most column that has data.

 

[Henner]

Hi,

The new code you provided works perfectly. Thank you very much indeed for your help - you're a genius.

Best regards,
Paul