Tech Problem Aggregator

Solved: XLS Macro to delete specific rows/columns and move columns to rows.

Q: Solved: XLS Macro to delete specific rows/columns and move columns to rows.

I have been working on a project for a few weeks and I could really use some help. I'm attaching a dummy file (example1) that is an example of how my XLS comes to me each month.

I need a macro that leaves me with the second file (example2). If the macro could just grab the Student name, then put the following info for each student all on 1 row, that would be perfect:

Child's Name | Date In | Time In | Rounded | Time Out | Rounded

Sometimes for some reason there is a student with a (Cont) after their name. I need that row deleted and just pull the times up onto the same row as other times. The example2 file might make sense.

Here are the steps I am doing manually that I hope a macro could do for me!
Un-merge cells: B10
Un-merge any cells with a child's name such as Doe, Jane
Delete any row with a childs name that has (Cont) after it such as Doe, Jane (Cont)
Delete rows: 1-9
Delete columns: A, C, F, H, I, J, M, N, O, and P
Manually move all info up to one row per student
Delete all empty rows below
See example2 as the completed file but I only moved a few of the rows per student for the sake of time.

Is there any way to do all of this with a macro?

A: Solved: XLS Macro to delete specific rows/columns and move columns to rows.

16 more replies
Answer Match 161.4%

I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

Thanks in advance.

A:XLS Macro to move data to columns, delete empty rows, delete duplicate columns

11 more replies
Answer Match 150%

I have data that repeats hundreds of times. I am trying to put each string of data on 1 line.

f-name(A2) initial(B2) l-name(C2) ID#(D2) DOB(E2) Street(F2) company(G2)
City(F3) company#(G3)
Home Phone(F5)
Work Phone(F6)

After the data is moved to one line I would like to delete the lines that previously housed the information. The company# can be deleted. I have never worked with macros. I would appreciate any help given.

A:Solved: move data from rows and columns to other rows and columns


3 more replies
Answer Match 142.2%

I have a flat file that when I read it into excel it populates column 1 of each row with data.
This data is actually a series of 5 fields that I need to have in columns and rows, that is,
column a1 thru a5 I need placed in a1, b1,c1,d1 and e1 followed by
column a6 thru a10 placed in a2,b2,c2,d2 and e2.
The data is balanced, that ism there is data in each of the 5 fields for a "record".
Any help would be appreciated.

A:Solved: excel macro to move columns to rows

8 more replies
Answer Match 140.7%

I'm a novice excel VBA macro user. I've manipulated already created macros, but I'm not sure how to create one from scratch. Can someone please help me create a macro.

I'd like to move the values highlighted in yellow to the cells highlighted in green...and then delete any rows that are empty.

Thanks in advance for the help.

A:Solved: Excel Macro to Move Data in rows to columns

9 more replies
Answer Match 132.6%

I've long used a simple macro on various spreadsheets to reset the last used cell:

Sub Reset_Range()
End Sub

This works fine, but prior to using it, I would have to press CTRL + End to see where the current last cell is, then manually delete any blank columns and rows appearing at the end of the data, by selecting them by their column letters / row numbers, right clicking and selecting Delete. Failure to do this would mean the macro didn't successfully correct the last used cell.

I would like to add this stage into the macro.
I found some code on the web that allegedly removes all blank rows and columns, so I pasted it at the top of my existing macro:

Option Explicit

Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:
If Selection.Rows.count > 1 Then
Set Rng = Selection
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
RwCnt = RwCnt + 1
End If
Next Rw

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On ... Read more

A:Solved: Excel 2003 macro to delete blank columns & rows, then reset range

6 more replies
Answer Match 130.8%

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

· Delete column C
· Delete all rows without account numbers (Acct numbers will always be in column A)
· Delete all rows that have dates in column F other than “00/00/0000”.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?

A:Excel 07 MACRO to delete rows/columns w/ criteria

Delete all rows without account numbers (Acct numbers will always be in column A)Click to expand...

If there are no acct numbers, will there be

blanks ""
Space " "
dash -
other ??
Can you provide a sample with dummy data so we can see the data structure?

1 more replies
Answer Match 130.8%

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

· Delete column C
· Delete all rows without account numbers (Acct numbers will always be in column A)
· Delete all rows that have dates in column F other than “00/00/0000”.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?

A:Excel VBA Macro to delete rows/columns with criteria

To delete column c, use:
Delete all rows without account numbers (Acct numbers will always be in column A)
If Cells(y, "A").Value = "" Then Cells(y, "A").Delete shift:=xlUp

lastrow = cells(rows.count,1).end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, "A").Value = "" Then Rows(y).EntireRow.Delete
If Cells(y, "F").Value = “00/00/0000” Then Rows(y).EntireRow.Delete ' Delete all rows that have dates in column F other than “00/00/0000”.
Next y


1 more replies
Answer Match 124.2%


I have data in the following row format:

0 4591 5 4491 10 4461 15 4441 20 4321

The data are X and Z points so the above example shows 5 pairs of data. I would like to place these pairs of data sequentially in two columns where one column has all the X's and the other has the related Z's. I have many, many rows of similar data that I need transferred into two columns.

0 4591
5 4491
10 4461
15 4441
20 4321

Could anyone help me out with a macro that can do this for me?

Thanks very much in advance.

A:Solved: Excel Macro - Rows to Columns

Welcome to the board.

Let's say the example pairs are in A1:J1.

With A1:J1 selected,

Sub test()
For Each Cell In Selection
If Cell.Column Mod 2 = 1 Then
Range("A" & Rows.Count).End(xlUp).Offset(1) = Cell.Value
Range("B" & Rows.Count).End(xlUp).Offset(1) = Cell.Value
End If
Next Cell
End Sub

gives you actual pairs in A2:B6.

So, what do we need to adjust to make it compatible with your "many, many rows of similar data" scenario?

3 more replies
Answer Match 123.6%

I do a weekly report that I base on results that I get with search done in Internet Explorer. I can then cut and paste these results into an Excel spreadsheet, but I need to delete most of the information (whole rows) and reorganize the remaining rows by taking the even numbered rows and putting them into the B column, then delete that Row as well. I have included some images that should help. A few more items:

1.)The information that I paste into the excel spredsheet may contain up to, but no more than 350 lines from the original copying source in Explorer (I'm not sure how many lines that translates into Excel)

2.) After every 20 records in Explorer there is a "top" link that gets copied, which needs to be taken into consideration when deleting the extra rows entirely

3.) Another way to look at this is that I ONLY want to keep the rows that have the 7 digit number a space then 2 more digits, as well as the rows that contain the price with the $ For instance the first record in my example I ONLY kept 8055312 11 & $70,000. The price will ALWAYS have a $ and the first set of number will ALWAYS have 7 digits first, no letters.

Attached is a jpg that shows the various steps, the last screen shot has a few of the cells highlighted in the upper left hand side. Those 6 highlighted cells is all of the information I need, and how I need it presented from the first 3 search records, the MLS number and the price.

A:Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns

16 more replies
Answer Match 123%

Hi Again

CodeLexicon gave me a code that worked very well in hiding column A only. Is it possible to make the macro hide all columns with all zeros?? Also I tried to change CodeLexicon's macro to do hide all rows whose columns have zeros but without success. I tried to make the code generic.

Attached is a workbook with the 2 macros. Test the macro that I did out and see that it only hides row A only.


A:Solved: Macro to hide all rows whose columns have zeros

6 more replies
Answer Match 116.4%

I have a very large spreadsheet (i.e. too big for pivoting and transposing wouldn't work) which looks something like this:
Col. ACol. BCol. CCol. DCol. ECol. FCol. GEntry1Info124562131654632435435Entry2Info150256874654242454654Entry3Info2221333333336546548463
I want it to look like this (i.e. flip Col.C through Col. G and line it up with Entry1, then do the same for Entry2, etc.):

Col. ACol. BCol. CEntry1Info12Entry1Info456Entry1Info2131Entry1Info6546Entry1Info32435435Entry2Info150Entry2Info25Entry2Info6874654Entry2Info242Entry2Info454654Entry3Info222Entry3Info133333Entry3Info333Entry3Info654654Entry3Info8463

Please help!

A:Move Data from Columns to Rows (w/o pivot)

11 more replies
Answer Match 115.5%

In my work environment we are writing test cases and for the test steps we use a word table. Its the same 4 columns so we can load them into the Quality Center environment. One issue we had was our review tool doesn't support word but does text documents. The format of the table was kind of messy when we saved as a text document in the sense it would lump all the stuff together with no clean breaks. As a solution people making every other row and column blank so there would be a break in the text format. Once the review was complete you would have to then go back into that word doc and delete all the blank rows and columns to load into Quality Center. Not hard but time consuming with the number of test we would deal with. I wrote a quick macro that would delete the header in the table plus all the blank rows and columns.

Now what we have is we are re using these test and adding to them. Currently we are pulling them out of quality center, adding the blank rows and columns back in, adding our updates, sending back out for review, deleting the blank rows and columns (macro does this), load back into Quality Center.

There may be a better way to go about this but what I am after is this. A macro that will insert a blank row and column every other row and column. I have been off and on playing with this for a few days but really have gotten no where with it. Posted on a few non technical forum and they suggested posting on this site for some help. I tried searching and did... Read more

More replies
Answer Match 115.5%

Hi, I'm new to macro. Hope you can help me with my problem. I've been stuck with this for a week with no result.

I have this table:

001 002 003 004
111 115 119 123
112 116 120 124
113 117 121 125

Let's say, the first row are the StoreID and all other digits below them are the ProductID

I want to convert this table into something like this:
001 111
001 112
001 113
002 115
002 116
002 117
003 119
003 120
003 121
004 123
004 124
004 125

I haven't made any macro before in MS Excel and this one is really getting the breath out of me.

Hope you can help me please.. I'd really appreciate it.

Thank you in advance!

A:Help with Macro in excel - Convert Rows to Repeating Columns

is this just a "one off" thing or do you have many sheets to convert? Because for one or a few sheets, it is not a lot of work to do manually...probably take a few minutes ( I 'll explain if you need)

to automate I can do it, but I need to build some loops and references to refer to the columns and ranges to move to a new's a bit of work.

Please confirm also if it is only 4 columns, and or if the column count varies

1 more replies
Answer Match 114%

In Sheet one I have some raw data which in sheet two I have picked out the useful pieces and have then input into out work forms. The problem is that I need to fill the form across (column) but in the sheet that it is referenced from I need it to take the data from the row


sheet one
....a.. b. c
1 a1 b1 c1
2 a2 b2 c2
3 a3 b3 c3

I need to find a way to automatically get it to do this referencing sheet 1 in column A and then filling to the right but actually having it go down the rows in sheet 1
sheet 2
...a.. b ..c
1 a1 a2 a3
2 b1 b2 b3
3 c1 c2 c3
the idea is that when the software exports to sheet 1 we will be able to print sheet two in the form we need it without having to make any changes. I know my explanation is a little crude but if someone could help me I would appreciate it. thanks.

A:Excel problem. Fill columns I need to move across rows in another sheet

You could use:


in Sheet2!A1, then replicate that down an across (pro forma style).

Would that help?

1 more replies
Answer Match 113.1%

"Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way"

Hello, all! Hopefully somebody can help me with the following problem (please see the attached document).

Presently, I have an Excel 2010 document containing variables 1-6 per country in rows for label 1 through label x. However, I need the table to contain the country level information as columns for each label so that there is only one row per label. For example, instead of row 1 containing label 1 argentina variables 1-6 then row 2 containing label 1 arab emirates variables 1-6, I need the table to contain row 1 label 1 columns argentina variable 1, argentina variable 2, etc...then arab emirates variables 1, arab emirates variable 2, etc.

Also, the countries in the table that do not have information for a given label fall at the end of the list for each label instead of in alphabetical order, but they need to be placed in alphabetical order when "columnized." I have provided the arab emirates as an example. Alphabetically, it comes before Argentina, but in the list it comes after because it did not contain information for those given labels. Since information per country will vary from label to label, the countries must remain in alphabetical order in the columns regardless of whether information is provided or not per label.

Is there a macro that may be written for this?

Thank you so much, in advance, for taking the time to read my post! Happy day!

A:Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way

14 more replies
Answer Match 110.7%

I need a visual basic macro for excel 2002 that will do the following:

From sheet1:ColumnA
Select rows until sheet1:columnA value does not start with REC (as the no of rows is unbalanced, sometimes 7 rows, sometimes 10 rows, sometimes 8 rows, etc)
copy selected rows and transpose to sheet2:columnA to however many rows were selected

Repeat until end last row
My real table has 30000 rows and the rows are unbalanced

I am attaching a test file.

Please help me and thank you for your support and time.

Tech Support Guy System Info Utility version
OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
Processor: Pentium(R) Dual-Core CPU E5300 @ 2.60GHz, x86 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 2047 Mb
Graphics Card: ATI Radeon HD 4300/4500 Series, 1024 Mb
Hard Drives: F: Total - 99998 MB, Free - 30856 MB; G: Total - 205236 MB, Free - 153405 MB; H: Total - 305168 MB, Free - 180367 MB;
Antivirus: None

A:Visual Basic Macro for Excel 2002 columns to rows in new sheet using unbalanced data

Can you also attach a second workbook showing how the data should appear after the macro is run?


3 more replies
Answer Match 110.7%


I am trying to classify and code my inventory items in excel.
My classification sheet contains data in columns which I would like to copy to rows in another excel sheet.
Here is the sample data in ACMOTORS-ATTRIBUTES sheet:

The above sheet has to be converted to columns as in Item_Classificatios_Form sheet :

Can anybody assist me in writing a macro for the same.


A:Solved: Excel Macro to convert Rows from one sheet to columns in another sheet

8 more replies
Answer Match 105.9%

Hi all. First post so be kind! Bit of a novice at VBA, but i am sure this is possible..
basically, I have a contact list in Excel that I need to format 'correctly'. It is currently formatted with the address on multiple rows, with phone number and email address in another column ( i have attached a dummy file). I would like to 'move' data so it is in correct columns...
Name Address1 Address2 Address3 Address4 Address5 Phone Email

To make matters a bit trickier, the number of address rows varies, so it might be 5 rows or sometimes 3; and not all contacts have an email address (although they would always be in row "2" of a contact address.

Many thanks in advance for any advice


A:Solved: Moving rows to columns

12 more replies
Answer Match 105.9%

I had a similar post and thought it I understood the solution, but did not. Solution called for Pivot tables and it's not quite working for me. Just wondering whether a VBA file would better serve the purpose. Please look at the attached file. Many, many thanks for the kind help. (Running Excel 2003 on an XP- SP3 machine.)

A:Solved: Convert rows to columns, (again)

7 more replies
Answer Match 105.9%

When I attempt to insert a column or a row, I'm getting a message from MS Excel:
"Cannot shift objects off sheet".
Can someone advise me as to what I must do to correct this?
Also, what have I done to cause this to happen?
Thank you for your help.

A:Solved: Inserting Columns or Rows

hi floydcojacket,

I think this is connected with your earlier issue with cell comments.

I'm now sure you must have your Advanced Options, Display Options for this Workbook- Display Objects set to Hide.

That option is in the next block below the Display options for Comments.


2 more replies
Answer Match 105.9%

I am really new to Excel (2003) and have read several similar posts to mine below; but cannot adapt the existing programming to accomplish the task. I wish to convert a data in “Book A” to appear like that of “Book B”
This will be an almost daily repetitive task. Please refer to the attachment. Thanks for looking. Any thoughts on the subject will be greatly appreciated.

System information:System Board: ECS K7S5A Chipset: SiS 735
CPU: AMD @ 1200 MHz
System Memory: 4x1024MB (DDR SDRAM)
Bios: AMI (11/21/01)
Video: onboard
Audio: Creative SB Live!
Op System: Win XP - Pro SP3 with all updates

A:Solved: Rows to Columns Issue

6 more replies
Answer Match 105.9%

I remember seeing somewhere that you can convert rows to columns in Excel, where the numbers would be across the top and the letters down the side. How is that accomplished? Also, if there is data in the spreadsheet at the time, will it be converted too or will it just change the tabs?

A:Solved: Converting rows to columns

7 more replies
Answer Match 104.7%

I need to convert rows to columns in Excel like this:

Monday | 1
Tuesday | 2
Wednesday | 3
Thursday | 4
Friday | 5
Monday | 6
Tuesday | 7
Wednesday | 8
Thursday | 9
Friday | 0


Monday | Tuesday | Wednesday | Thursday | Friday
1 | 2 | 3 | 4 | 5
6 | 7 | 8 | 9 | 0

Is this possible?

Well, that didn't display at all like I expected. In short, I need the repeating headers in column A to become one set of headers in row 1 with the data corresponding to each header name below it. Does that make sense?

A:Solved: Excel Rows to Columns with a Twist

Try this :

1) copy the days i.e(MondayTuesdayWednesday Thursday Friday Monday Tuesday Wednesday Thursday Friday
2) Go to the required cell and right click and select "paste special as" then select "Transpose"
You will get the data in this shape
MondayTuesdayWednesday Thursday Friday Monday Tuesday Wednesday Thursday Friday
4) Repeat the same step for other data............

2 more replies
Answer Match 103.5%

Hi all,

this came up in conversation recently and I thought I knew the answer but was proved wrong.

when you insert a row (or column), it sometimes (but not always) copies the format from the preceeding row (or column).

what are the criteria that determine this?

I thought it was based upon something like if the preceeding 5ish rows were all the same format but it's not that!


Just realised I don't know how to add a link to a previous post!!
btw I used the macro in the posting 11-Aug-2004 "Insert Row Automation" which solves the above nicely (thanks Anne), but I'm still curious for the above criteria.
btw the 'InserARow' macro needs a row test at the beginning to check that you aren't insering above row 1 'cos there aint a row 0 to copy down from!

More replies
Answer Match 101.4%

for some reason, this afternoon they began showing only ONE column, on the left side of the folder. the remainder of the folder is blank. see attached screen shot.
thanks for helping, boyd.

Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: AMD Athlon(tm) II P340 Dual-Core Processor, AMD64 Family 16 Model 6 Stepping 3
Processor Count: 2
RAM: 2810 Mb
Graphics Card: AMD M880G with ATI Mobility Radeon HD 4250, 256 Mb
Hard Drives: C: Total - 288256 MB, Free - 112291 MB; D: Total - 16683 MB, Free - 2407 MB; F: Total - 99 MB, Free - 89 MB;
Motherboard: Hewlett-Packard, 1444
Antivirus: AntiVir Desktop, Updated: Yes, On-Demand Scanner: Disabled

A:Solved: Folders no longer show rows and columns of icons/thumbnails.

Thanks for the screen shot; makes it very clear what you are describing.

If you select a file it will preview it in the Preview pane (maybe not all types of files). If you'd like the Preview pane smaller and the main area larger drag the divider to the right. If you don't want the Preview pane at all get rid of it (Organize - Layout - remove check from Preview pane).

3 more replies
Answer Match 101.4%

Excel 2000

I have created a calender ( actually a schedule) in excel which has multiple rows columns (A9:O36). The schedule shows where everyone works that day (eg billy on fries, johnny on grill, susie on cashier)

At the bottom I want to create one box which shows what days each worked and where

..................Fries | Grill | Cashier |

I've tried Sum. sumif, countif, if-then even an array...
Can't seem to do it or find the answer through searching
The closest I've come is the array, but I don't know how to make it work for a range of cells that is not a column
Any thoughts would be appreciated.

A:Solved: count instances that occur in multiple rows/columns after criteria met

9 more replies
Answer Match 99.3%

Copy Rows into columns using VBA1 have a very little experience with VBA, and I would really appreciate any help with this issue. I need to convert rows into columns from sheet 1 to sheet 2.input file Article ID EU-statistics Number of sheets Size of sheet Absorbancy With empties BOM (Empties Bill of Material)1062 a 0 b 0 c1065 a 0 b 0 c1066 a 0 b 0 c1071 a 0 b 0 c1077 a 0 b 0 c1081 a 0 b 0 c1086 a 0 b 0 c1090 a 0 b 0 cDesire output file Article ID 1062 EU-statistics a1062 Number of sheets 01062 Size of sheet b1062 Absorbancy 01062 With empties BOM (Empties Bill of Material) c1065 EU-statistics a1065 Number of sheets 01065 Size of sheet b1065 Absorbancy 01065 With empties BOM (Empties Bill of Material) c1066 EU-statistics a1066 Number of sheets 01066 Size of sheet b1066 Absorbancy 01066 With empties BOM (Empties Bill of Material) c

More replies
Answer Match 99.3%

Isn't it possible to freeze a specific row or column, rather than panes?

A:freezing rows/ columns

If you are talking Excel then you can freeze the Top rows and side columns as headings, but I don't think you can freeze individual rows/columns anywhere else on the worksheet.

2 more replies
Answer Match 99.3%


I do not know much about excel so i need your help.
Attached is a screenshot. Please examine it a bit to know which rows and columns the data responds to.

Now that you done that heres the question:

How can i switch the dates to the top row and the exercises to where the dates are but keeping the readouts of the weight measurements to its corresponding labels?
I looked over it and it seemed like i had to do it manually but i am wondering if there was a way to automatically do it? Without all the hard work

A:Flip the rows and columns

Check out the function =Transpose()

Type into the top cell you want to put the new table (I'll use A32), and using your screenshot values:
In cell A32: "=Transpose(A1:K27)" (don't use ") after you type that in, select starting at that cell the same area for your new table - all the cells.

So select A32 and select to AA42, then press F2, hold down Ctrl & Shift, and then Enter, and it will Transpose the vertical to the horizontal and all the corresponding data.

Transpose is brilliant.

I recommend that you then copy the information from that and use paste special and keep values, then delete the complete rows with the transposed array, its annoying cos you can't change the info, so just turn it back into cell data is the best option.

Unless you want to put it on another sheet, and want it to update from your original data.

3 more replies
Answer Match 99.3%

I have formulas that has the data setup in rows (input). The next sheet is formulas that are in columns referencing these individual rows (output). When column A's formulas are dragged to column B, the references change as expected but to Sheet1!B1 rather than Sheet1!A2 (back to the rows). I think I 've done this before, but can't for the life of me remember how. Help me please and I'll be your best friend! (hey, it's the best I can do).



A:Excel Columns to Rows

Select all the cells involved

Right click anywhere on your selection and from the menu that pops up choose Copy

Click in the cell where you want the list to begin then Right click and from the menu choose Paste special you will now see another menu.

At the bottom right hand side of this menu, Select Transpose.

That's it your list will now run across the page instead of down

3 more replies
Answer Match 98.4%

how can the contents in cells A1, A2 and A3 be transposed so that they are on the same row, AND always on the same row as B1; and

how can the contents in cells A4, A5 and A6 be transposed so that they are on the same row, AND always on the same row as B4.

a sample worksheet is attached.


A:excel - transpose columns to rows

10 more replies
Answer Match 98.4%


I would like to put names or numbers on the columns
and rows.

Ive managed to get a table to work for me, its a set
of multiples for 45
the first cell is blank, the next is the first entry
and i put 45 in this case here, the rest of the table
fills itself in with multiples of 45.

Thing is, when i print it out, i want the columns and
rows to have headings.

Is there a way to do this please?

The drawing is only aproximate, i have to convert to
bmp, then add in the extra numbers, which dont change
they could be background, the colour doesnt matter it
is only there to separate the columns and rows from
the multiples of 45.
Later if its 46 or 47 i would use the same table.

<img src="">

by using the tens and units you get the multiple at
the intersection.
row 20 and column 2 give the result for 22 times 45

There might be better ways, but this works ok.

Regards, John

A:EXCEL, Can i put headings on rows and columns??

I set up the rows and column headings the way you want.
Then I hid the "real" row and column headings.
You can left-click to view it.
Right-click and choose Save target as to save it.

2 more replies
Answer Match 98.4%


I have imported sales data in excel. The information lists in along two rows with mutliple columns. I need the information to be in one row under various headings. I have attached a link to a sample to explain this properly. I think I need to use a macro but I don't understand enough about them to make it work.

Any help would be much appreciated.

A:Moving data from columns to rows

11 more replies
Answer Match 98.4%

I have some Excel files that only take up 50 - 100 rows, but the scroll bar on the side is very tiny (as if I had used the worksheet to the bottom). Is there a way to get rid of these unwanted rows at the end? I've highlighted and deleted them, but they just get replaced. Is there a simple way, withouht copying the data to a new file?


A:Excel - Getting rid of extra rows/columns

Copy and paste the cells into a new worksheet. By default, Excel opens a Workbook with 3 Worksheets. After copying andpasting the info, nad verifying that itlooks the way you want, then you can right-click the worksheet tab at the bottom and delte the old one.

3 more replies
Answer Match 97.2%

Okay, got a convoluted spreadsheet going on & I'm not entirely sure how to describe it in words. I tried several searches on these forums and on Google but I didn't find anything that was relevant. So I'm going to apologize in advance if I'm re-posting a topic due to my inability to use the appropriate vocabulary to describe this problem.

Windows XP Professional SP3
Excel 2007

I've attached an example spreadsheet.

Here's the simplest way I can describe it:

1) Test1 is the first spreadsheet I'm working with
- All the data is already filled in by a vertical organization
- This spreadsheet will be updated on a regular basis

2) I'm trying to have the data in the columns moved over to another sheet
- Horizontal organization (This can not be compromised - Absolutely necessary)
- This is being done by formulas (of course)
- This needs to be done on separate pages, i.e.: Jack should have his own page, Jill should have her own page
- It would be preferable to find a way to use the Fill command, as there are more records currently and more coming (or at least a way to automate the proper filling)

3) Is there any way in which this can be done with merged cells? If not, then I don't mind doing it with un-merged cells
- Merged Cell Version is "Test2" Sheet
- Un-Merged Cell Version is "Test3" Sheet

Both the Vertical & Horizontal Based Spreadsheets are going to be updated on a regular basis but the firs... Read more

A:Non-Adjacent Formula Filling - Columns vs Rows

13 more replies
Answer Match 97.2%


does anyone know how to search for the text content of a cell through a column and then return the value/content of the cell of a certain other column of the same row where the text content was found?

A1 B1 C1 D1
2 013004T XY
3 092004TP YZ
4 012004TP ZY
5 052004P YX
6 092004TP YY [FORMULA]
7 072004TP ZZ
>>FORMULA: "Look for string '092004TP' in column A (other than in row 6) and return the value of that row in column C (C3=YZ)."
NOTE: There might be more than 1 row with the string '092004TP' in column A - I would need the sum of all results in column C where the FORMULA finds this string.

I also have a similar problem with another worksheet:
It would require a formula which searches through a whole column for certain names, e.g. "Donna", and then returns the value of another column in the same row where it found "Donna"; the value, again, is not in a column directly adjacent to the column where "Donna" would be found.
This is fairly identical to problem (A), except that the search string would be unique (only 1 result), and it doesn't have to take into account (and disregard) if the string was found in the same row of the FORMULA (the search column would be in a different worksheet from the FORMULA worksheet).

Thanks for any suggestions!

from New Zealand


A:EXCEL Searching Data in Rows/Columns

7 more replies
Answer Match 97.2%

I have data in columns - starting A2 - P2 (and another set of data from A3-P3)

I have various formulae using that data, starting in A20-A40.

I wish to include the second 'row' of data (the A3-P3) into a second set of formulas from B20-B40.

Filling 'right' doesn't work, because, if the formula in the A column is something as simple as "=A2+C2+D2", when I fill right, the formula in the B column is "=B2+D2+E2" - when I NEED it to read "=A3+C3+D3"

I realize there is probably a simple workaround for this - in fact, I COULD just rekey the formula...the reasons I don't are 1) the formulas are more elaborate than I listed, 2) there will be many more rows of data (at least 15 more) - so, rekeying the formula for EACH row of data would be time-consuming and 3) it will eventually move the formulas off the same 'visible area' as the data (I'm currently combatting that by having the panes frozen).

Is there any way to accomplish what I'm trying to get done?


A:Creating Formulas in rows from Data in columns

9 more replies
Answer Match 97.2%


I need help in Excel copying columns and paste it in rows.

Ex: I have Columns A and B.
Column A Column B
5.78% 4.51%
23.60% 32.50%
14.17% 16.41%
32.12% 54.16%

Now output should be in one row.

Row1 5.78% 4.51% 23.60% 32.50% 14.17% 16.41% 32.12% 54.16%

A:Copy columns and paste in rows in Excel

sub test()
dim rng as range
dim i as long
dim col as long
dim nextcol as long
dim lastrow as long
lastrow = range("A"&rows.count).end(xlup)
set rng = range("A2:B"&lastrow)
nextcol =col+1
for i=2 to lastrow
range(cells(i,1),cells(i,col)).copy cells(1,nextcol)
end sub

1 more replies
Answer Match 97.2%

I have an excel file with single worksheet. The file is about 4-5 print pages in A4 size. The excel file has titles in first column and 5th row. I want to repeat these titles in every pages when printing. How do I do this. Can I use macros for this.

A:Repeating rows and columns for printing in excel


1 more replies
Answer Match 96%

I know you can hide and unhide rows and columns but can you make it so they don't even exist?

I have a just under 2m picture I want to put as a background but when you put the file as a background it tiles it the whole 256 columns and 65,536 rows even if I have them hidden.

This makes my 2.5m spreadsheet over 32 megs in size even with the columns/rows hidden.

I need to be able to see the lines on this above the picture for this project and I can't find any way to do this. I thought I could put it in the background but this pesky problem turned up.

I tried adding more space to the jpg that was empty white space but this too also increased the file size.

A:Excel - Pictures and Limiting the number of rows and columns?

8 more replies
Answer Match 96%

We have a survey package which is great, but it out puts the questions numbers over the columns and each new row is a response, however our calculations are setup for the question numbers to be the rows and the responses are the columns. Is there any way to swap these values around in this fashion? Thanks for any help

A:Excel: swapping columns and rows layout of data?

Howdy. You can copy the data, then Paste Special > Transpose

2 more replies
Answer Match 95.7%

Is there anyone who could help me with one macro? This is the format of the data:


And this is what I would like to have at the end:


So basically I need a macro that would copy column D&E and put the data below B&C, also copying tasks respectively. and then the same for all other columns. The problem is that the number of columns varies... sometimes it's 30, sometimes it's much more.

Thanks in advnace

A:Solved: macro to move columns

Welcome to the forum fenrost.

Is there always going to be 7 tasks?

1 more replies
Answer Match 95.7%

Please Help - ASAP - Thank you in Advance.
(I did also post this under Business Applications, before realizing it should probalby go here - sorry about that)

I'm just STUCK on this one last bit of my code to finish off this macro for a meeting today

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

Maybe I'm making this sound too difficult... at this point all I need is:

Columns A-E, K, P, S-U, W, AB-AC, AE-AH, AJ-AK, from the "TargetData" (Defined Range) on Customer Targets worksheet of Test1, to Paste into Columns A-S of the Build Worksheet

This should be easy right? I'm having a mind block...

A:Excel Macro - Move Specific Columns from Range to New Range - ASAP If Possible

There have been a bunch of views, but not replys or suggestions. Am I not including the right information? Is this not posted in the right place? Can someone please help me?

2 more replies
Answer Match 95.1%

Please Help - ASAP - Thank you in Advance.

I'm just STUCK on this one last bit of my code to finish off this macro.

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

I think I've attached a sample of the code, and some test workbooks I've been using.

Any advise would be helpful. I know this should be easier than I'm probably making it...

A:Excel Macro - Move Specific Columns from Range to New Range

16 more replies
Answer Match 95.1%

Ive spent so much time on this, I want to pull my hair out.

I have a pdf file of addresses. I converted the pdf to an excel file (the one I uploaded).

My eventual goal is to convert the addresses into excel, then do a mail merge into word.

I found a tool (via this great site) called ASAP utilities that I can turn the rows into columns, thats easy.

But the problem is, the addresses aren't always the same length. Some labels are 4 rows, others are 6 rows.

Is there any way I can either automatically make the rows 6 each without doing it manually? Or does anyone have any type of suggestion?

Thanks so much for the help

A:Converting excel rows (addresses) to columns for mail merge

Firstly, can I assume that you have permission to use those addresses for your own mailing?

If so you could go back to the originator and ask them to give you a CSV file, or maybe see if the pdf file can be saved as a CSV file.

If so, it would be very easy to bring that into Excel and then convert the file from text to Columns, using the Data, Text to Columns feature.

1 more replies
Answer Match 95.1%

..... and I want to convert the lot to 2 decimal place numbers.

Excel has several methods, but none seem to work for conversion in bulk. Working on futher cells from a suitably formatted one does not work.

Is the only way to solve my problem to manually enter the numbers elsewhere, clear the original cells, and then cut and paste back to the original cells?

Help please!

A:Number data in 10 columns and 25 rows pasted from other source is text

Need more info about the source text file. When opened in Word and Show All (the paragraph mark) is enabled, what does the text file look like?

See this thread from a couple years ago and see if it gives you any ideas:

Please Help - Data in Text File to be arranged in Excel in a specific


3 more replies
Answer Match 95.1%

Well..glad I found this forum. I would like to be able to populate a word document from an excel worksheet, with the ability to select (highlight) multiple rows in excel and have those rows transferred to word, but only certain columns

I found almost the right macro here:

Except you can only select (1) row.

Any coding solutions on how to copy a range of rows? This will also require the .Text in the word paste to change....
I see some hope with this code as well, but figuring it out may take me longer than someone that does this everyday.
Thank you.

A:Populate Word Doc from Excel (Multiple Rows, Select Columns)

12 more replies
Answer Match 95.1%

I am un able to get an existing word file to open. I can get the following to open a new word file. I need this to opoen an existing file.

Sub export2Word(row)
Dim oWD As Object
Dim wdDoc As Object
Dim TWB As Workbook, wsh, wsh1 As Worksheet
Set TWB = ThisWorkbook
On Error Resume Next
Set oWD = GetObject(, "Word.Application")
If Err.Number <> 0 Then Set oWD = CreateObject("Word.Application")
Set wdDoc = oWD.documents.Add
With wdDoc
With .Range.paragraphs(1).Range
.Text = "PICK UP & DELIVERY" & Chr(10)
.Font.Size = 15
.Font.Name = "Times New Roman"
.Font.Bold = True
.Font.Underline = True
.ParagraphFormat.Alignment = 1
End With
With .Range.paragraphs(2).Range
.Text = "PICK UP" & vbTab & Cells(row, "K").Value & vbTab & Chr(10)
.Font.Size = 10
.Font.Name = "Times New Roman"
.Font.Bold = True
.Font.Underline = True
.ParagraphFormat.Alignment = 0
End With
With .Range.paragraphs(3).Range
.Text = Chr(10) & Chr(10) & Chr(10) & Chr(10)
.Font.Size = 13
.Font.Underline = False
.DefaultTabStop = Application.CentimetersToPoints(1)
.ParagraphFormat.TabStops.Add Position:=Application.CentimetersToPoints(5), _
Alignment:=0, Leader:=0
End With
With .Range.paragraphs(5).Range
.Text = "NAME" & vbTab & Cells(row, "B").Value & vbTab & _
Cells(row, "C").Value & Chr(10) & _
"ADDRESS&qu... Read more

A:Populate Word Doc from Excel (Multiple Rows, Select Columns)

Welcome to the forum
You should paste your code in a code clock

[ code ]

no spaces in between the []

[ / code ]

Set wdDoc = oWD.documents.Add

To open a word doc

Set wdDoc = oWD.documents.Open(fullpath and name of the file)

This should do the trick

1 more replies
Answer Match 94.5%


I am trying to learn rules in altering a macro that has been recorded. I need the following code to execute across columns A-AX, where each column is to be sorted independently of the others. Thank you in advance for your time.

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A2:A21")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
End Sub

A:Solved: How to execute a macro across a specific number of columns

7 more replies
Answer Match 93.3%

I sure hope someone can provide, or direct me to the formula to accomplish the following in Xcel 2000.
I have a singe sheet consisting of 5,123 rows of data in 13 columns. A person has been maintaining this and we are about to convert it to cvs and install a text file in a CGI\Perl program to run on a web site. For some unknown reason the person who started and has been maintaining the file has set it up so that the first 3 columns contain data that should be all in one column. Here is an example: a ham radio call, we will say is: WW6XXX. In the first column (A), row 7, he has entered the prefix: "WW", in the second column, (B), row 7 he has entered the call district "6" and in the third column (C), row 7, he has entered the suffix "WWW".

I have inserted a blank column "D". I can combine, for example A7,B7 and C7 with the simple formula =A1&B1&C1 placed in D7. No problem.

Now the problem, and I'm sure you all ready can see it. I need to do this with all 5,123 rows. It certainly SEEMS, logically, that I should be able to accomplish this but durned if I've been able to do it. I have Googled, I have Jeeved it, you name it, I've tried it. I have found some "close calls" that almost answer it, but not the real answer.

Obviously I can do this 5,123 times, manually, but I'd rather find a simpler answer, if there is one.
Blessings to anyone who can provide me with the answer. This by the way, is for a... Read more

A:Solved: In Xcel, I Need to combine multiple columns containing multiple rows into one column

9 more replies
Answer Match 91.8%

Hi There

It has been quite a while since I asked for help. I wonder if the following is doable as an Excel macro. The attached is only a demo with only 2 fields and a few rows of data. The real worksheet has more fields and around 3,000 rows.

Sheet 1 contains the data in its initial state and sheet 2 contains data after the macro is run. You will see from sheet 2 that the field labeled QUANTITY determines the number of rows to insert and copy the correct data into those inserted rows. I want the entire row to be copied down as opposed to only the data, since I have more columns than shown here. Is this possible to do? Can anyone help me with writing a macro, since I have over 3,000 rows to do.

Thanks for all your help. You guys are fantastic.


A:Inserting specific number of rows in Excel and copying data in those inserted rows

14 more replies
Answer Match 91.5%

Hi All,

I have a spreadsheet containing 30 address blocks that I'm trying to clean up. A sample is attached.

All of the data is in column A. Each address block is 6 rows, like this:

Company Name
Street Address
Phone number
Email Address

Between each 6 row block is a varying number of additional rows that might or might not contain extraneous information. I want to remove those extra rows leaving only the address blocks.

I'm thinking about counting the "x" number of rows between email address "@" symbols, and then just deleting X-6 rows. But I'm not sure how to accomplish this.

Any ideas how this might be accomplished, or any better suggestions to accomplish the goal?

A:Solved: Excel macro/vba to delete rows

"might or might not contain extraneous information"

For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
If InStr(Cell, "@") <> 0 Then
Cells(Cell.Row - 5, 1).Resize(6, 1).Copy Range("B" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell

If those are real names and addresses, you should remove your file.

2 more replies
Answer Match 91.2%


I have a worksheet that contains 2 columns of data. I have listed a sample of the data as below column A and column B

...............Column A....................................Column B
Row tall dog
Row hungry mouse
Row big short dog
Row black table
Row tall tree

I have some questions on how to do the things I want.

1) I want to excel to search the whole document and detect rows with cells that start with the word 'black' and cells that start with the word 'big'. Delete the whole rows.

results to return will be
Row big short dog

2) I want to search Column A only, delete the rows if the cells in Column A ends with '.net'

results to return will be
Row tall dog
Row hungry mouse

3) I want to detect cells that contain the word tall. If the word tall is detected in Column A or Column B, delete the row.

results to return will be
Row hungry mouse
Row big short dog
Row bla... Read more

A:Solved: Delete rows when specific words are found

16 more replies
Answer Match 90.6%

I am trying to find a way to find a specific text within a worksheet, select that cell and the 9 rows under that row, and delete the 10 rows.

A:Solved: Excel Macro -> Delete Multiple Rows

16 more replies
Answer Match 89.4%

Good Day,

I would like to copy the rows in a worksheet to another worksheet where a value in the row say H2 <900. I would then like to delete those cells from the former worksheet. Can you assist with this. Thanks

A:Solved: Macro to delete rows where cell meet a condition

7 more replies
Answer Match 88.2%

I was hoping someone could help me write or find a macro that would take two columns and move it to column A under the previous data and repeat for all columns. Basically user information is located in pairs of columns (typeofvalue, value). So column A and B are for user1, column C and D are user2, column E and F are user3, etc. Bonus if I could have a space in between each once moved.


HTML Code:
User1First User1Last User2First User2Last
User1ValueTypeA1 User1ValueB1 User2ValueTypeA1 User2ValueB1
User1ValueTypeA2 User1ValueB2 User2ValueTypeA1 User2ValueB1

HTML Code:
User1First User1Last
User1ValueTypeA1 User1ValueB1
User1ValueTypeA2 User1ValueB2


User2First User2Last
User2ValueTypeA1 User2ValueB1
User2ValueTypeA1 User2ValueB1
Each user has 47 rows of "values", for the example I just did two rows worth.

A:Excel Macro to move columns to 1 row after each other.

I did a quick google and found this. It worked for you?

Excel macro to move columns to 1 row after each other

2 more replies
Answer Match 87.9%

I need to delete multiple columns within an Excel worksheet based on the headers in the first row. What would be the best way to perform this operation?

I have been playing around with the code below, but I feel as though there could be a better way.

Sub test()

Dim myArr As Variant

myArr = Array("Test1", "Test2", "Test3")
If Range("C1").Value = myArr Then
Columns("C").Delete shift:=xlToLeft
ElseIf Range("D1").Value = myArr Then
Columns("D").Delete shift:=xlToLeft
End If

End Sub

A:Solved: Excel Macro -> Delete Multiple Columns Based on Criteria

16 more replies
Answer Match 87.3%

I have a dump from a text file in two single columns. The first column is a block of 55 labels that repeat for every group of data, the second is the data for 110 transducers. Every transducer produce 55 single values (numerical or status(text)). I need to order this two columns dumps into a table. Where the first column are the labels and the rest 110 column the corresponding data one column per transducer.
How can we do this? Can any body help me?

A:Excel macro to move two Columns into a table

Hi and welcome to TSG!

Can you upload a sample of the textfile? I just need a few lines. It doesn't have to be real data. Just want to see the data structure.If you can provide this, I'm sure we will solve the problem.


1 more replies
Answer Match 87%

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

A:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub

2 more replies
Answer Match 87%

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you

A:Excel Macro to Move data in Rows to column

8 more replies
Answer Match 86.1%

Hi Gurus,

I have many excel files containing similar data.

1. I want to copy specific columns (columns A to U) from all files and paste the consolidated data into another excel file.

2. I also want to copy specific columns (columns C,D,E,G,H,K,L,T,V,W,X,Y) and paste the consolidated data in a different excel.

Please help.

A:Excel macro - copy specific columns from different files to one file

Are all of these source Excel files stored in the same directory? Do they need to be opened and consolidated in any particular order? More details please. If possible please try to provide a sample file with any sensitive data removed.


1 more replies
Answer Match 84.3%

I have found a few posts here and there about how to move columns around but nothing quite what I m looking for.

So I get these data dumps from a vendor weekly , column headers are always the same but they are always in different order (strange I know)


Email | Phone | Last Name | First Name

I would like to have a macro cut/paste entire rows and put in a specific order

First name |Last Name| Phone| Email

Any help would be great! Thank you!

A:Excel 2007 – Macro (newbie) - Move Columns bases on name question

How many total columns are we talking about? Is it possible to post a sample workbook with any sensitive data removed so I can see the structure.


3 more replies
Answer Match 84%

can any body suggest me a macro code to delete the blank rows from the worksheets, i have a worksheet in which blank or empty rows are present unevenly. and i want to delete all such blank rows all at once. so please suggest me a code.

A:how to delete blank rows in a worksheet by macro?

I referenced this utility at MSDN, and tested it.
This may or may not be what you are looking for
============== =====================================
Place the following VBA code in the worksheet you want to delete blank rows from
' The following code deletes blank rows from the active worksheet.
Dim Counter
Dim i As Integer
Sub DelRow()
' Input box to determine the total number of rows in the worksheet.
Counter = InputBox("Enter the total number of rows to process")
' Loops through the desired number of rows.
For i = 1 To Counter
' Checks to see if the active cell is blank.
If ActiveCell = "" Then
' Decrements count each time a row is deleted. This ensures
' that the macro will not run past the last row.
Counter = Counter - 1
' Selects the next cell.
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub
The solution can be found at.....
You may further be able to programmatically create a form with a button that will automate the process.
Hope this helps

3 more replies
Answer Match 82.2%

I am trying to write a macro that compares cells in column A from sheet1 to cells from column A in sheet2 and if the values from sheet2 are not in sheet1 then I want to delete the entire row from Column A in sheet2.

for example

In sheet1, column A has a list of id no's (no duplicates) and in sheet2 Column A has list of id no's (with duplicates). I want to delete every row in Sheet2 Column A that is not in Sheet1 Column A.

Keep in mind that both sheets have several columns.
Also there is about 5000 rows in sheet 2

Any help would be greatly appreciated.


A:Excel HELP macro to Delete Row Based 2 columns from 2 sheets

Welcome to the board.

While I understand each part of your post (I think), some parts seem to contradict others. So here's what I did.

In Sheet1!A1:A5 I entered 1 -- 2 -- 3 -- 4 -- 5.

In Sheet1!A1:A6 I entered 1 -- 2 -- 6 -- 3 -- 4 -- 5.

Then I wrote some code that removes the third row from Sheet2, since it contains 6 in column A which is not in col A of Sheet1.

How the code works is:

(i) inserts a new column A on Sheet2

(ii) enters a formula in new column A Sheet2, e.g.:


which returns a number if there's a match in column A Sheet1, #N/A if not

(iii) deletes any rows on Sheet2 with #N/A in column A

(iv) deletes column A on Sheet2 (redundant).

Obviously if this isn't what you need don't use it, post more info instead.


Sub test()
x = Rows.Count
y = Sheets("Sheet2").Range("B" & x).End(xlUp).Row
Sheets("Sheet2").Range("A1").Resize.FormulaR1C1 = "=MATCH(RC[1],Sheet1!C,0)"
Sheets("Sheet2").Range("A1").Resize.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
End Sub

1 more replies
Answer Match 81.3%

I have the following data that i need to create a set of rules or code that if multiple criteria is met to delete the row. I have included some examples of what i need. i tried doing a Recording macro but didnt work since sometimes some of the criteria is not there at the time of running the macro and it fails.

I need to have code look for specific values, i.e.

Account paid Y
Date of Payment Blank
Highlight all cells
'this is to review information
after this then check if it has Code 3 = C1 and Code 4=D1
Highlight all cells
'this is to review information

Check if Code 1=B2
Check if Paycode is blank or other
Highlight all cells
'this is to review information
Then delete other rows

AccountBalanceDateCode 1Code 2Due dateCode 3Code 4Code 5Account paidPaycodeJonnathan5009/1/2011A1B1C1D1E1YCollectedJessica252.359/2/2011A2B2C2D2E2YCollectedMaria321.549/3/2011A3B3C3D3E3NPayPlanLeon652.339/4/2011A4B4C4D4E4NPayPlanDavis525.329/5/2011A5B5C5D5E5YCollectedCarmen277.679/6/2011A6B6C6D6E6NOtherKarina346.869/7/2011A7B7C7D7E7NJose 677.659/8/2011A1B1C1D1E1YCollectedMary550.649/9/2011A2B2C2D2E2YPayPlanMiguel302.999/10/2011A3B3C3D3E3NPayPlanJessica372.189/11/2011A4B4C4D4E4NCollectedElaine702.979/12/2011A5B5C5D5E5YOtherPatricia575.969/13/2011A6B6C6D6E6NCollectedKaren328.319/14/2011A7B7C7D7E7NCollecteddavid397.59/15/2011A1B1C1D1E1YPayPlanJoe728.299/16/2011A2B2C2D2E2YPayPlanJoseph601.289/17/2011A3B3C3D3E3NCollectedwilliam353.639/18/2011A4B4C4D4E4Nbill422.829/19/2011... Read more

A:MS Excel 2007 - VBA or Macro to delete rows based on multiple criteria.

8 more replies
Answer Match 81%

Hi guys, I have this macro which is below and what i need it to do is only delete the rows that do no contain the number "6" in it. i have over 30k lines many which are either blank or have other values. I only want to keep the rows that contain the value 6 in the C Column.

Sub DeleteDuplicates()

Dim x As Long
Dim LastRow As Long

LastRow = Range("C30000").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("C1:C" & x), Range("C" & x).Text) > 1 Then
Range("C" & x).EntireRow.Delete
End If
Next x

End Sub

If you need a sample i can attach one.
Thanks ahead of time.

A:Solved: excel macro to delete rows not containing "6"

HI Chris,

Can you try this codes mentioned below , i have used auto filter for deletion of rows , but u need to mention the column number in the field and also the entire range of the sheet.

Sub DeleteDuplicates()

With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
'.Range("I2:I" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr
.Range("$a$1:$c$" & .Rows.Count).AutoFilter Field:=3, Criteria1:= _
"<> 6"

Set Rng = Nothing
With .AutoFilter.Range
On Error Resume Next
Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
On Error GoTo 0
If Not Rng Is Nothing Then Rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

End Sub

Hope its helps you...

3 more replies
Answer Match 107.52%

Ok I am trying to print a spreadsheet format list. I have about 30000
title-artist entries in 2 columns. I am basically trying to print pages 1 & 2, 3-4, 5-6 and so on the same page without shrinking them way down. I want it to be just like I printed 2 columns but with 4 instead. I want to use the left side of the page for page 1 and the left side for page 2. Any help would be greatly appreciated.


A:Solved: Printing 4 Columns from 2 columns in excel

Welcome to the board. ?

1 more replies
Answer Match 106.68%

I have a macro that performs a sorting function and the data in column "I" gets pasted into columns J,K,L once it is separated and sorted. The issue is that I have existing data in those columns.
I figured that If I add to the macro that already exists a command to add three columns to the right of "I" this would fix my issue...yes?no?maybe?
I have been googling to find insert macros, but they all seem to be weird ones (e.g. insert a column every 96 cells etc.)

A:Solved: Macro to insert 3 columns to the right

There's a button 'Mark Solved"

The text SOLVED does not show up so people will uneedingly open this post.

1 more replies
Answer Match 106.68%

Hi All, fantastic site.

I need to run a macro in excel to sum the following.

check row 3 col 3 or row 3 col 4 for a value > 0 then subtract this value from row 3 col 5 return answer to Row 3 col 6 if col 5 cell is empty then go to previous cell that has a value >0 return this value to Col 6 on the same row as col 3 or 4 (the one with a value >0)
There should be an answer in every row col 6 this could run upto 100 rows+

Hopefully i've explained myself engough for you to understand. Ta

A:Solved: Macro to sum 3 columns using a loop

6 more replies
Answer Match 106.68%

Hi everybody,

I can't get the following macro proper.

I need a macro which copies S1:V64 (In S7 starts the data, but I think it is more easy tot start at S1) to another column. Column W till Z. When the macro is pressed for a second time, cells S1:V64 (or Cells W1:Z64) needs to be copied till AA till AD et cetera. I have attached an example of my page. The thing is that the row with totals, should move to the right. So this should be always at the end of it.

So does someone know which macro I need?

Something like (it's not correct, I don't know how I have to write it correct.

Selection S1:V64
But then I need the row to be inserted before the Total row. Is someone able to help me?
Hope it's clear what my purpose is?

Thanks in advance!

A:Solved: Insert columns macro

Not necessary anymore! Found another solution.

1 more replies
Answer Match 106.68%

I tried out this formula, =IF(B10<>"",COUNTA($B$10:B10),""), to number the rows in a column that have data in adjacent columns, but leave a row un-numbered and uncounted if the adjacent cell was blank. It worked great in Column A.

However, I tried using the formula in column D and it didn't work so well. Instead of numbering in sequence it was counting by 3's. 3, 6, 9, 12 etc. It still skipped the rows with no data in the adjacent cells but the numbering was wrong.

I tried making some changes no no avail.

This is the formula in D10: =IF(E10<>"",COUNTA($B$10:E10),"")

Any ideas on how to make this formula work in Column D?


A:Using =IF(B10<>"",COUNTA($B$10:B10),"") to number rows in columns other than A


I think your problem is with your formula having a static reference for the first part of the array in the COUNTA (The dollar signs). Since you have the reference in the original location as $B$10:B10, when you copied it over, it kept the reference so your new location is counting across columns B through E. You can fix this 1 of 2 ways. You can modify only the formula in column D to read =IF(E10<>"",COUNTA($E$10:E10)"").

Alternatively, you could change your original formula and recopy it. The dollar signs will keep the reference they are in front of static, so if you changed your original formula to =IF(B10<>"",COUNTA(B$10:B10),"") and copied it, the column label would change relatively.

The final recommendation I'm going to give is a new formula that I think gives you the same result but with easier formulas.


This will give a true/false value based on whether or not the targeted cell is blank, and then return the current row number. Let me know if you need any further assistance!

5 more replies
Answer Match 106.26%

Hi All

I am wondering if this is possible. I have a requirement to remove unwanted rows from an excel sheet that is originally generated by an Informix database and that includes additional rows of data that are extraneous and surplus to requirements.

Now, I appreciate that amending the report within the Informix database is the ideal solution, but right now we are unable to do this, so what I would like to do is to amend the sheet itself and see if a Macro can be used to remove the unwanted rows.

The criteria I see in the sheet that could be used are bold rows i.e. I want to remove any rows from the sheet that are not bold...? Is this possible in a Macro?

Alternatively, I can use an absolute reference to rows that are not required, but as there are a variable number of rows (dependant on the report) in this case I'd be unsure about the logic that defines when (at what row) to stop deleting i.e. there are no more rows with data in them.

Is this possible please folks...?



A:Solved: Stripping out rows in a report via a Macro (or other)

13 more replies
Answer Match 106.26%

I have the following macro I created to merge A1-A4 to one cell, how do I run the same for the entire sheet? I have 975 rows on my spreadsheet

DimoutputText As String
delim =" "
On ErrorResume Next
For Eachcell In Selection
outputText= outputText & cell.Value & delim
Next cell
.Cells(1).Value= outputText
.HorizontalAlignment= xlGeneral
.VerticalAlignment= xlCenter
.WrapText= True
End With
End Sub

A:Solved: copy macro to remaining rows


Try this ... it assumes that the columns you are merging are A-D and that the first row is Row 1.
Sub JoinAndMerge()
Dim lRow As Integer
Dim col As Integer
Dim i As Integer
Dim outputText As String
delim = " "
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lRow 'Change the "1" to the first row you want to change
For col = 1 To 4 'Change "1 to 4" to the columns you want e.g. "5 to 8" would be cols E to H
outputText = outputText & Cells(i, col).Value & delim
Next col
Range(Cells(i, 1), Cells(i, 4)).Clear
Cells(i, 1) = outputText
With Range(Cells(i, 1), Cells(i, 4))
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
outputText = ""
Next i
End Sub


2 more replies
Answer Match 106.26%

How do I move between columns in word? I know how to create columns. I know if I go to the bottom of one column it continues on the other. But I'm interested in writing an address on the left and jumping to the right and writing another address. I'd like to move back and forth between the columns w/o going to the bottom of the page. Is there a way. Thanks, Jex

A:Solved: Move Between Columns In Word

If you mean within a table - TAB

3 more replies
Answer Match 105.42%

I need to create a macro in Excel that will look for a certain text within a column (Column A), Copy the data in another column (Column B), and paste the data in another column (Column C).

For Example....

Column A

Column B

So, I would like to copy all the data in Column B that correspond with "ABC1" in column A, and paste that data in Column C.

A:Solved: Excel Macro Search and Copy Rows

16 more replies
Answer Match 105.42%

Hi, I have approx 25,000 rows of data relating to parts held in inventory, on a spreadsheet in Excel 2003 SP2 .

One common cell on each line is the Supplier Stock Code and the data in it is duplicated on different rows as the same Supplier Part is held under several different Part ID's on our system, ie,

Cell A2 0-320311-00
Cell A3 0-320311-00
Cell A4 0-320311-00
Cell A5 0-330317-29
Cell A6 0-330317-29
Cell A7 0-330317-29
Cell A8 03400BK08345
Cell A9 03400BK08345
Cell A10 03400BK08345
Cell A11 03400BK08345

I have sorted the data into Supplier Stock Code order and need to find a way to automatically insert a blank row (or two) after each change, ie, in the example above it would be between rows 4 & 5, and 7 & 8.

Does anyone know of a Macro which will do this?

Many thanks,


A:Solved: Excel Macro to automatically insert rows

First things first -- welcome to the board.

2nd thing:

"insert a blank row (or two) after each change"

Hmm: for what purpose? The "official" line is "DON'T do that, it's VERY bad design". K?

So what I did was drag your last entry down to r25000, to generate 03400BK08346,
03400BK08347, and so on. "Test data".

Then I ran the code at the bottom of this post. It ground away for 12 minutes (make coffee, visit bathroom, yada yada) & then fell over on account of "ran out of worksheet" (bear in mind I had c.25K unique values, you have far fewer).

Last (but most definitely not least) thing: back up your work before trying the code.


Sub test()
Application.ScreenUpdating = False
LastVal = Range("A" & Rows.Count).End(xlUp)

Do Until LV2 = 1
x = Range("A" & Rows.Count).End(xlUp).Row
x = Range("A" & x).End(xlUp).Row

If Cells(x, 1) = LastVal Then
LV2 = 1
y = Application.Match(Cells(x, 1), Columns(1), 0)
z = WorksheetFunction.CountIf(Columns(1), Cells(x, 1))
Cells(y + z, 1).Resize(2).EntireRow.Insert
End If

Application.ScreenUpdating = True
End Sub

2 more replies
Answer Match 105.42%

Hi... I'm somewhat new at Excel and really new at Macros.

This is what I'm trying to do:

I have two workbooks -

on the first workbook I have about 1000 rows of informtion with the following six colum headings...
ID# Last First Mid Dorm Decal#

The first five colums are all filled in already. I have to put the Decal#'s in as I get them.

What I need is that when I put a Decal# in (lets say F23) for a particular person, a Macro (or Formula) would then trigger that row (A23:F23) to be copied and then pasted to the next available blank row on a sheet that is located in a different workbook.

The first workbook is called -
Student List_Decals Input (SheetName is ResidentCommuterList)

The second (target) workbook is called -
Veh Plate_Decal Info Master (SheetName is Master Plate Info List)

I don't know if a Formular can handle this type of thing or if a Macro would be better... but if anyone has a good suggestion, I would be greatful.


A:Solved: Macro to Copy & Paste Rows to Different Workbook

11 more replies
Answer Match 104.58%

What would be the best way to find a specific Column Header, and copy the data found in the column until the column header is found again?

A:Solved: Excel Macro Copy Data From Columns

8 more replies
Answer Match 104.58%

I'm using some software to export payment information into a CSV file. This file needs to be in the bank's specified SIF format which requires all the data to be in one column.

The limitation with my software is that the detail of each payment gets exported into it's own separate column. For instance, cells A1:A10 contain payment details to one individual, B1:B10 to another, and so on. I require the whole lot combining into Column A, running from A1:A65536 (it will never go this far down the spreadsheet, but you get the idea).

I've tinkered with some basic copy and delete macros but I'm unable to make the headway that I need to. I don't have the touch when it comes to programming .

Thanks in advance

A:Solved: Excel: Macro to combine all Columns into Column A

12 more replies
Answer Match 104.16%

I have a drop down list in D1 and what I need is a macro that will search for the selected value through column D and hide the rows that don't match. Think of it as like the autofilter function in excel.

A:Solved: Excel macro to hide rows if value does not match header

9 more replies
Answer Match 103.32%

I am having exactly the same problem as a closed thread.

Did anyone ever get to the bottom of it?

Basically, if I have a table in Excel 2007, and actually use the filter, it then breaks the autofilter.

I had a table with 6 rows, and it completely messed up.

Now I am down to 4. If I filter on 1, then 1 shows.

If I cancel the filter, only 1 to 3 show and row 4 is hidden. If I drop down the filter, row 4 does not exist.

If I unhide row 4, it is matterless. Excel 2007 's table shows it on screen, but it is no longer in the autofilter.

Filter by row 1, then row 3 disappears into hidden status, and drops off the autofilter. Rows 1, 2 and 4 now show (I unhid row 4, remember?)

BUT< the autofilter now only has row 1 in it. Rows 2 to 4 have been lost from the autofilter.

I need to be able to :
a)automatically add rows
b)filter by criteria
c)reference the table by row and column in calculations

Switching the autofilter off and on does not help. Converting it to a range means all my references went to pot, and also means that referencing and calculating sums breaks it automation. I am utterly depressed.

A:Excel 2007 table filter bug is hiding rows and losing rows from the autofilte

Ah well, Looks like I'm not the only one who had this problem. This article has a detailed explanation of what's causing the problem and a few possible workarounds:

I found it thanks to

2 more replies
Answer Match 103.32%

Hi I am trying to copy and insert rows based on a number in cell E. If the number in cell E is 4, I would like to copy 3 additional rows beneath the original and then also number the 4 rows into column F consecutively for each group. I am attaching a spreadsheet of before and after. Sheet 1 is the before and sheet 2 is the after. Can anyone help me with this. I have tried another code but it not work properly. I did find one that would put blank rows in but I cannot get past that. Any input is greatly appreciated.

Thank you


A:insert rows based on number in cell and copy the data down into the new rows

Hi Peg

Try this solution and see if it is ok for you. I've put in some code to help prevent the running of the code multiple times on the same sheet. If you don't need this safeguard you can delete the block of code that does this.

Also, make sure and make a backup of your file before running this - just in case.

1 more replies
Answer Match 102.9%

Weird and annoying.

Suddenly after a reboot my 5 row layout of Metro screen was changed. Tiles are now huge, Metro only showing two rows:

This on laptop's 1600 * 900 display. If I set my external 1920 * 1080 display as primary, it shows the same huge ties in 3 rows:

Register value in HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\ImmersiveShell\Grid is 5 as it should be.

Any ideas on how to get my 5 row layout back?


A:Metro tiles suddenly huge, only 2 rows, should be smaller and 5 rows

Hello Kari,

It looks like you may have turned on the Make everything on your screen bigger option under Ease of Access in PC settings. Turning it off should restore things back to normal.

3 more replies
Answer Match 102.06%

Excel Masters, I am not sure how to loop through the following code for multiple row formatting. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the 'else' statement to format the ranges for all rows with a cell selected?
Private Sub Button_DeleteRow_Click()
Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then



Selection.EntireRow.Interior.ColorIndex = xlNone
With ActiveCell
Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
End With

End If

End Sub

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.

A:Solved: Excel Macro - Loop selected-cell formatting for multiple rows

Something like:

Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then



Selection.EntireRow.Interior.ColorIndex = xlNone
SelRows = Selection.Resize(, 1).Cells.Count
Cells(ActiveCell.Row, 27).Resize(SelRows, 14).Interior.ColorIndex = 15

'(and so on)

End If


On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.Click to expand...

If you mean "how do I bypass that for Selection.EntireRow.Delete then:

Application.EnableEvents = False
Application.EnableEvents = True

2 more replies
Answer Match 102.06%


I couldn't find an answer in the Forums for this in the past half hour of looking through them- there are so many, and I need an answer sooner than in the 4 or 5 days it will take me to get through them in HOPES of finding my answer. Sorry. So could someone who knows Excel in Windows 7 help me?

I uploaded a bunch of keyword searches. Came up with 4 columns and anywhere from 100- 400 rows. I need to sort these in different ways. By Keyword so the spreadsheet is by alpha, then by Demand with highest at top/lowest at top, by Supply highest at top/lowest at top, and by Profitability highest/lowest. I know I can't do all at once, I am referring to different sorting methods.

Example: (you have to imagine 4 columns, and the numbers going under each column- when I saw the preview it bunched the numbers together and I can't seem to put them where they belong, but the first # goes under the second col (B), second # goes under "C", third # goes under "D" column:



breathing exercises 7995 5306 1507
celebrations 8523 142301 60
cervical 10235 58395 175
chakra 28455 43099 660

My problem has been that in reading the instructions I don't understand how to do this (perhaps I'm just dumb!) so that each word will still have the "righ... Read more

A:Re: EXCEL- resorting rows so they each relate to original rows

When you sort in Excel, you select all of the rows and columns of the data to be sorted. When you apply a sort to one (or many) columns, all of the data on each row is kept complete and moved up or down.

Is that what you needed to hear?

7 more replies
Answer Match 101.64%

Ok another challenge.. I wish I could do this myself, but I struggle with the code.

Is there a way to have a macro check the line below it, and if it is a duplicate row, delete it

A:Solved: Im sure it can be done.. Delete Duplicate Rows

16 more replies
Answer Match 100.8%

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it

3 more replies
Answer Match 100.8%

i tried looking around for a similar macro that would delete all columns except few based on the name in row 1, but all i could find is deleting rows based on condition, not deleting columns as i need and doing the additional things that i need..

i need this macro to do couple of things, here is the breakdown:
1. remove all columns except few based on row 1 name
2. remove entire row based on a condition
3. rename some titles in row 1
4. reorder the entire sheet based on date in order from oldest to newest

first i'll explain my excel sheet then below is the bits of code that i think is correct, just need to put it together...

this is my excel make things easy, i will only write out the names of the columns i would like to keep.

Column A, row 1: Supplier
Column B, row 1: Quantity
Column C, row 1: UOM
Column D, row 1: (blank) --- i also want this column removed, but has no name
Column E, row 1: Destination Type
Column F, row 1: Item
Column H, row 1: Item Description
Column I, row 1: Location
Columns J - L -- remove
Column M, row 1: Subinventory
Columns N - W -- remove
Column X, row 1: Order
Columns Y - AP -- remove
Column AQ, row 1: [] -- yes, this is what its called when exported from the database
Column AR -- remove

1. i need to remove the columns from above. I have a code that will delete the items that i dont need by writing out the column names,
but i want it the other way around so it will delete everything except what i need (this macro below i... Read more

A:Solved: delete all columns except some with particular headers in row 1

any help with this would be great, thanks!

1 more replies
Answer Match 100.8%

Afternoon everyone,

I've got a question. I created this code, but I get the error: "Block If without End If". The idea is that the administrator can delete multiple records (rows) based on what he selects, using check boxes.

I put this code:
Private Sub DeleteRecords_Click()
If CONTACTS.Select Is Not Null Then
RunCommand acCmdDeleteRows
End If


A:Solved: Delete multiple records (rows)

Problem fixed,

used the wrong syntax for the object and IF command.

1 more replies
Answer Match 100.8%

I am trying to delete rows where the value in the 4 column is less than 21. I have tried variations of the code below but all do the following:

Delete some rows that are less than 21 and leave others
Delete some rows that are greater than 21 and leave others

When I step thru the code and check the value it shows me the value in the cell and deletes whether it is true or false. Please tell me what I am doing wrong.

LastRow = ActiveSheet.UsedRange.Rows.Count
For r = LastRow To 1 Step -1
With Cells(r, "D")
If .Value < "21" Then .EntireRow.Delete
End With
Next r

A:Solved: Excel VBA Delete Rows by Value Not working

Duh! Don't need " around 21 and changed to

LastRow = ActiveSheet.UsedRange.Rows.Count
For r = LastRow To 1 Step -1
If (Cells(r, 4).Value < 21) Then Cells(r, 4).EntireRow.Delete
Next r

1 more replies