| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro delete columns satring row 23
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro delete columns satring row 23
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 On 08/11/2010 09:24, dii jii wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro delete columns satring row 23
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro delete columns satring row 23
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!!! On 08/12/2010 09:39, dii jii wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete Columns Macro | Excel Programming | |||
| create a macro to delete columns and then border remaining columns | Excel Programming | |||
| Need Help Delete Columns Macro PLEASE! | Excel Programming | |||
| Macro to Delete empty columns | Excel Programming | |||
| Macro to delete columns | Excel Programming | |||
Linear Mode
