DELETING BOTTOM ROWS IN MULTIPLE WORKSHEETS

[venumkd]

Dear Sirs,

I need a Macro to do like this.

I am having a workbook containing up to 1500 worksheets. In this workbook, few worksheets (up to 100 sheets) are to be identified or selected and in these sheets certain bottom rows are to be deleted.

The sheet names shall be mentioned in the First worksheet in Column A and the row number or name (starting dell name of the row, which are different in each sheet) from where the rows to be deleted to underneath shall be mentioned in Column B against each sheet name mentioned in Column A.

Thanks Regards

VENU

 

[mumps]

the row number or name (starting dell name of the row, which are different in each sheet) from where the rows to be deleted to underneath

Can you give us a few examples from your data what you mean by the above quote? Also what is the name of the first sheet containing the sheet names? Do the sheet names start in row 1?

 

[venumkd]

Sirs,

First sheet name is Sheet1
Sheet name starts in Row1 to down in Column A ie in cell A1, A2, A3 and so on to the required numbers
I will explain. This is for a Data Entry work.
There are around 12000 sheets in the workbook out of which only 1500 are main sheets and the rest are blank sheets.
The sheets are named like this:

Sheet1

Sheet 01

Sheet 2

Sheet 02

Sheet 4

Sheet 5

Sheet 03

Sheet 7

Sheet 8

Sheet 9

Sheet 04

Sheet 11

Sheet 12

Sheet 13

Sheet 14

Sheet 05

And so on up to 12000 sheets, out of which 1500 sheets (Underlined) are main sheets and balance sheets in between (non Underlined) are blank sheets.
The sheet1 (Itallic) is actually inserted by me to provide input data such as Sheet names to be identified or selected and row numbers of the identified / selected sheet from where the rows to be deleted to underneath. This Sheet1 (Itallic) may be considered as the first sheet. This sheet will be deleted after the work.

It is like this that:

The sheet 01 may be having 7500 rows, but I need only 4501 rows and hence all the rows under row 4501 to be deleted.

The sheet 02 may be having 8000 rows, but required is only 6000 rows and hence all the rows under / below row 6000 to be deleted.

The sheet 03 may be having 10000 rows, but required is only 7499 and hence all the rows under / below row 7499 to be deleted.

In all sheets the rows will be more than required and the excess rows are to be trimmed off,

In sheet1, Sheet names shall be provided in Row1 to down in Column A ie cell A1, A2, A3 and so on to the required numbers.

The row numbers of the sheets (row cutting / trimming / deleting point) shall be provided in Column B against each sheet names in Column A,

If there was a provision to upload the file in your system, I would have uploaded the file itself. This file is unable to upload as Image format.

I hope the matter is clear to you and expecting your valuable suggestion.

Thanks & Regards

VENU

 

[mumps]

The sheet 01 may be having 7500 rows, but I need only 4501 rows and hence all the rows under row 4501 to be deleted.

Will column B for sheet 01 have 4501 or 4502?

 

[venumkd]

Sir
Column B for sheet 01 have 4501
Thanks & Regards
VENU.

 

[mumps]

This macro assumes that the main sheet names in column A of Sheet1 are underlined.

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim LastRow1 As Long, LastRow2 As Long, rng As Range
    LastRow1 = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Sheets("Sheet1").Range("A1:A" & LastRow1)
        If rng.Font.Underline = xlUnderlineStyleSingle Then
            With Sheets(rng.Value)
                LastRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                .Rows(rng.Offset(, 1).Value + 1 & ":" & LastRow2).Delete
            End With
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[venumkd]

OK sir
Working OK
Thanks & Regards
VENU.

 

[mumps]

My pleasure.

 

[venumkd]

Sirs

There is a minor problem while using this VBA.

It delete / trims after / below one row of the input value. If we give the trim point value as 4501, It trim from 4502

Pl. advise the correction in code

Thanks & Regards

VENU

 

[mumps]

Delete the + 1 in the code.