Find Lastcell in Column and delete rows above with blanc cells in that column

aisietie · Feb 21st 2012

Hello,

I found a function that founds the last used cell in a column.

Code

Public Function GetLastCell(ByVal LastCell As Range) As Long
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long


Set WS = Worksheets("Sheet1")
With WS
    Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
    LastCellRowNumber = LastCell.Row
End With
End Function

Display More

Now I need to delete all the rows above that row with a blanc cell in that column.

So the macro must find the last cell in a column, and then delete all the rows above that have a blanc cell in this column.

Can somebody help?

Grtz

aisietie

**StephenR** · Feb 21st 2012

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

I doubt that code will work. You can use something like this:

Code

Function x(c As Long)


With Sheets("Sheet1")
    .Range(.Cells(1, c), .Cells(Rows.Count, c).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With


End Function

aisietie · Feb 21st 2012

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

Thx for the reply.

But is it possible to let it work with a commandbutton click?

Can this function be implemented in the commandbutton click event?

aisietie

**StephenR** · Feb 21st 2012

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

No because it takes an argument.

aisietie · Feb 21st 2012

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

OK, how can I let this or another function do the thing?

aisietie

**StephenR** · Feb 21st 2012

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

When do you want it to do its thing? If you assign it to a button, how does it know which column to work on? You could have an input box in which the column is entered and then the code runs. Or assign it to run when a cell in the column is double-clicked?

aisietie · Feb 21st 2012

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

At this moment I have a button that cut a row and paste the row in an other sheet.

Code

Sheets("Input").Range("A" & ComboBox1.ListIndex + 3 & ":ZZ" & ComboBox1.ListIndex + 3).Cut Destination:=Worksheets(strWs).Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

I would like, that after the cut, the empty row is deleted.
So i thought by checking if there is an empty cell in column A, then delete this row.

aisietie

**StephenR** · Feb 21st 2012

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

Just add the three lines and replace c with 1.

aisietie · Feb 21st 2012

Re: Find Lastcell in Column and delete rows above with blanc cells in that column

Stephen,

That is a big OK.

Thanks

aisietie

Find Lastcell in Column and delete rows above with blanc cells in that column

Participate now!

Share