Find and select last plus one row in a worksheet?

davidwh000

New Member
Joined
Jul 18, 2010
Messages
21
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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
 

davidwh000

New Member
Joined
Jul 18, 2010
Messages
21
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
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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.
 

davidwh000

New Member
Joined
Jul 18, 2010
Messages
21

ADVERTISEMENT

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.
 

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
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
 

davidwh000

New Member
Joined
Jul 18, 2010
Messages
21

ADVERTISEMENT

Brilliant, spot on, works exactly as I hoped.
Thanks everyone.
David
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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.
 

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
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
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,801
Messages
5,638,437
Members
417,025
Latest member
MusterDuster

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