Excel macro

  soldier boy 21:30 07 Aug 03
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 03

Which column, or does that change?

  VoG II 21:54 07 Aug 03

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 03

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

  VoG II 21:57 07 Aug 03

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 03

?

  Willow12 23:27 08 Aug 03

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

  VoG II 23:44 08 Aug 03

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 03

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 03

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 03

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.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

iPhone 9 and beyond: 32 amazing future smartphone developments - graphene, supercapacitor…