Macro delete columns satring row 23

  • Thread starter Thread starter dii jii
  • Start date Start date
D

dii jii

Hi,
Would like to get a piece of code ( excel macro) to delete Columns
starting row 23.. This is where my headers start.
I don't want include the entire column, i ahve some data above row 23.

Thanks in advance.

Jimmy
 
You may want to rephrase your question.

Do you want rows or columns deleted?

If you really meant rows, you could use something like:

Option Explicit
Sub testme()

With ActiveSheet
.Rows("23:" & .Rows.Count).Delete
End With

End Sub
 
Dave thanks...
Here is the details:
Geniri ID Year Cutoff Lot info
I23 I23 1991 23
I24 I24 1999 44
I25 I25 2001 33
I26 I26 2004 11
I27 I27 2006 15
I28 I28 2007 22

Parts Description Tring Vehicle type Make Year Color Cushion Auction
Lot
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900
Brakes Ultra Tweek Barakes 2 99 Mazda 2003 Black 3444 12900




After run ( Here is the desired result):




Geniri ID Year Cutoff Lot info
I23 I23 1991 23
I24 I24 1999 44
I25 I25 2001 33
I26 I26 2004 11
I27 I27 2006 15
I28 I28 2007 22

Parts Vehicle type Make Year Cushion Auction Lot
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900
Brakes 99 Mazda 2003 3444 12900


As you see the headers start in the middle..Any thing above headers
arestatic info, and would like keep there.

Thanks again
 
You can use edit|find to search for the "parts" string in the header. This
would give you the last row that should not be touched.

Then you could delete the ranges (not entire columns anymore) starting with row+1.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range

Dim NextRow As Long
Dim LastRow As Long

Set wks = Worksheets("Sheet1")

With wks
With .Range("a1").EntireColumn
Set FoundCell = .Cells.Find(what:="Parts", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Design error: Parts not found in column A"
Exit Sub
End If

NextRow = FoundCell.Row + 1
LastRow = .Rows.Count

.Range(.Cells(NextRow, "I"), .Cells(LastRow, "I")) _
.Delete shift:=xlToLeft
.Range(.Cells(NextRow, "f"), .Cells(LastRow, "F")) _
.Delete shift:=xlToLeft
.Range(.Cells(NextRow, "D"), .Cells(LastRow, "D")) _
.Delete shift:=xlToLeft
.Range(.Cells(NextRow, "B"), .Cells(LastRow, "B")) _
.Delete shift:=xlToLeft

End With
End Sub

The code looks for Parts in column A. If that string isn't found in a cell by
itself, it figures the data is wrong and stops right away.

Then it deletes ranges starting from the right and moving left. I used columns
I, F, D, B. But it was difficult to tell in a plain text message.

You'll want to check the column letters to make sure that they're correct. And
remember to update them in each line twice!!!
 
Back
Top