Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

Ensuring a spreadsheet has only 420 rows in Excel

Experts,

I have mulitple worksheets within a workbook.  I want each worksheet to have only 420 rows and no more.  Is there a way in VB, I can delete all rows after row 420.

I have the spreadsheet set to display only up to 420, but however I am performing a delete operation which deletes rows from the original 420 when there is no data on the respective row.
0
morinia
Asked:
morinia
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
I'm not a huge fan of this method I'm about to suggest, but you can hide all rows from 421 to the last row.  That will limit the display to row 420.

If you have a macro-based option that deletes the rows as you described, you could augment it to unhide an equal amount of rows below to keep the display at 420 rows.  

Regards,
-Glenn
0
 
Subramani NCommented:
Include this in your vba and try if it works.
Application.ActiveSheet.UsedRange
totalcount = Worksheets("SheetName").UsedRange.Rows.Count
If totalcount > 420 Then
    Rows("421:totalcount").Delete
End

Open in new window

0
 
Glenn RayExcel VBA DeveloperCommented:
Just a follow-up:  If you do hide rows 421-onward, they will stay hidden even if you delete rows in the visible area.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now