Excel macro

  soldier boy 21:30 07 Aug 2003
Locked

Hi Everybody

Does anyone know how to create a macro that will search a given column and find an empty cell. When it does find the empty cell I then want to delete the whole row before going on to the next empty cell and repeating the process until the end of the spreadsheet

best wishes

SoldierBoy

  VoG II 21:33 07 Aug 2003

Which column, or does that change?

  VoG II 21:54 07 Aug 2003

OK, I've assumed column F (6)

Sub DelRow()

Dim xRow As Integer, iRow As Integer, iCol As Integer

iCol = 6

xRow = Cells(Cells.Rows.Count, iCol).End(xlUp).Row

For iRow = xRow To iRow Step -1

If ActiveSheet.Cells(iRow, iCol) = 0 Then ActiveSheet.Cells(iRow, iCol).EntireRow.Delete

Next iRow

End Sub


Make sure that you backup the file before trying this.

  VoG II 21:55 07 Aug 2003

If ActiveSheet.Cells(iRow, iCol) = "" Then ActiveSheet.Cells(iRow, iCol).EntireRow.Delete

  VoG II 21:57 07 Aug 2003

ub DelRow()

Dim xRow As Integer, iRow As Integer, iCol As Integer

iCol = 6

xRow = Cells(Cells.Rows.Count, iCol).End(xlUp).Row

For iRow = xRow To 1 Step -1

If ActiveSheet.Cells(iRow, iCol) = "" Then ActiveSheet.Cells(iRow, iCol).EntireRow.Delete

Next iRow

End Sub

  VoG II 22:45 08 Aug 2003

?

  Willow12 23:27 08 Aug 2003

I think you confused the hell out of him! I know you did me!!! :~(

  VoG II 23:44 08 Aug 2003

Sorry, I thought if somebody asked for a macro they would know what to do. I was being silly, and of course they don't.

Soldier Boy

Open your worksheet and ALT+F11 keys together to open the Visual Basic Editor. On the menu bar Insert/Module. Then copy the following as far down as End Sub and paste it into the macro sheet in Excel

Sub DelRow()

Dim xRow As Integer, iRow As Integer, iCol As Integer

iCol = 6

xRow = Cells(Cells.Rows.Count, iCol).End(xlUp).Row

For iRow = xRow To 1 Step -1

If ActiveSheet.Cells(iRow, iCol) = "" Then ActiveSheet.Cells(iRow, iCol).EntireRow.Delete

Next iRow

End Sub

==============

ALT+F11 to exit the VBE. Tools/Macros/Macro, select DelRow and click the Run button.

NOTE: this macro deletes rows from the bottom up. It won't work the other way. Why? this question is Fateful Shadow's homework for tomorrow!. LOL.

  Willow12 23:50 08 Aug 2003

You've got to remember that you are the king of Excel (amoungst other things) and most people don't come close to your expertise.

Not wanting to hijack the thread, but once you have all of the columns that you need how do you delete all of the rest so that the cursor returns to the start of the next row when you have completed the last field?

Willow12

  VoG II 23:58 08 Aug 2003

Sorry its too late and I'm off for some shut eye.

I suspect that this is a different question. Please post it as such with an example and I'll pick it up in the morning.

  soldier boy 12:38 10 Aug 2003

Thank you for the reponses.

I did not confuse the hell out of me and I did understand the macro.

sorry I have been slow at responding! Busy man!

I will test it at work tomorrow

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iPhone X review

How to find a font: Discover the name of a typeface with these apps

The best iPhone for 2017

Comment créer un compte PayPal pour payer en ligne ?