Find and select last plus one row in a worksheet?

davidwh000

New Member
Joined
Jul 18, 2010
Messages
24
I would like to get the code for a macro that will allow me to automatically find and select the last plus one row in a worksheet and then use that to select to the end of the worksheet and clear all from that point forward please. I would also like to do the same for columns.
The report that is being produced has variable row count and has formatting in the blank cells which I would like to delete before using the report.
David
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Something like this?:

Code:
Sub clearFormatsFromBlankCells()
    Application.ScreenUpdating = False
    lastcol = Cells.Find(what:="*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
    lastrow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    Range(Cells(lastrow + 1, 1), Cells(Rows.Count, 1)).EntireRow.Delete
    Range(Cells(1, lastcol + 1), Cells(1, lastcol + 1)).EntireColumn.Delete
End Sub
 
Upvote 0
Thanks Weaver
It crashes giving me the following error.
Compile error:
Function call in left hand side of assignment must return Variant or Object
David
 
Upvote 0
What version/platform are you running on?

Also, where did you paste it to? It should be in its own module, not a worksheet module.
 
Upvote 0
I am running the spreadsheet on MS Office Excel 2003 and with XPPro OS. I did first put it into the maro in the workbook but have now copied it to its own module but get the same result.
 
Upvote 0
Try this

Code:
Sub ResetUsedRange()
   Dim wks As Worksheet
   Dim lngLastRow As Long, lngLastCol As Long, lngRealLastRow As Long, lngRealLastCol As Long
   On Error Resume Next
   If wks Is Nothing Then Set wks = ActiveSheet
   With wks
      With .Range("A1").SpecialCells(xlCellTypeLastCell)
         lngLastRow = .Row
         lngLastCol = .Column
      End With
      lngRealLastRow = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
      lngRealLastCol = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column
      If lngRealLastRow < lngLastRow Then .Range(.Cells(lngRealLastRow + 1, 1), .Cells(lngLastRow, 1)).EntireRow.Delete
      If lngRealLastCol < lngLastCol Then .Range(.Cells(1, lngRealLastCol + 1), .Cells(1, lngLastCol)).EntireColumn.Delete
      Debug.Print .UsedRange.Count
   End With
End Sub
 
Upvote 0
pboltonchina - interesting stuff, just wondering if you have any idea where the problem was with my code? After all, you can imagine it worked fine on my machine.
 
Upvote 0
Weaver,

Davidwh000 said your code crashed so I offered an alternative. It's a piece of code I got off this site that works for me. I didn't write it and I don't profess to understand it all, I was just trying to help.

I can't hold a candle to you guys and if I have upset you, I sincerely apologise. I just thought this site was about helping people solve problems, where ever that information comes from. I have had lots of help from many people on this site, including your good self, and I was just sharing that.

Regards
Paul
 
Upvote 0
Weaver,

Davidwh000 said your code crashed so I offered an alternative. It's a piece of code I got off this site that works for me. I didn't write it and I don't profess to understand it all, I was just trying to help.

I can't hold a candle to you guys and if I have upset you, I sincerely apologise. I just thought this site was about helping people solve problems, where ever that information comes from. I have had lots of help from many people on this site, including your good self, and I was just sharing that.

Regards
Paul
Sheeeeeit, no way - I just figured I ought to try to find out why mine was failing. Was a very unusual error message to boot. I guess I's better grab a copy of that code for myself. There's nothing worse than when a bit of code works on one setup and not on another. And it doesn't do to get precious!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top