Need help please deleting rows under certain criteria

Henner

Board Regular
Joined
May 16, 2002
Messages
174
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

FryerTuck

New Member
Joined
Jan 23, 2004
Messages
37
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

Board Regular
Joined
May 16, 2002
Messages
174
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

New Member
Joined
Jan 23, 2004
Messages
37
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

Board Regular
Joined
May 16, 2002
Messages
174
Hi,

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

Best regards,
Paul
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,160
Messages
5,768,530
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top