Excel 2007 :: After Deleting Rows - Next Row Does Not Move Up
Aug 13, 2011
I have a macro that exports a row (or rows) to destination sheet ("Amber") based on criteria in Column K. Once exported I return to the source sheet ("Register") and the row that had the data that I wanted exported has exported the info but the row just sits where it was and appears to only 'clear the contents'. The macro I use is:
Sub Extract_FW_Rows()
Set ws = ActiveSheet
endrow = ws.Cells(65000, 1).End(xlUp).Row
For i = endrow To 1 Step -1
[Code] ......
I set up the macro on Excel 2002 in work as thats what we have....however when I input the same code into my home laptop working Excel 2007, all works perfectly i.e. it cuts the data to destination sheet, goes back to source sheet and the rows have moved up accordingly - I do realise that the action taken is a row removal i.e. I had 1000 rows, macro executes, I now have 999 rows in the source sheet.
What I would like is the same result in 2002 as I get in 2007.
View 6 Replies
ADVERTISEMENT
Jul 9, 2008
Problem: We have some rather large excel spreadsheets (142,000 rows, 190 columns) and some users that need to delete the data from rows of cells 5,000 rows + at a time. When doing this machines jump in CPU, Excel goes "not responding" and on average comes back to life 1 minute 45 seconds after pressing the delete key.
Note: We are not doing a right click and deleting the rows. This is a highlight of the 5000 rows and pressing the delete keyboard key to clear the data.
We've tried disabling the Auto Save and the Workbook Calculation changed to Manual.
Dell is no help, Microsoft is no help. This process can be replicated on multiple Dell machines and with Operating Systems of XP SP2, SP3 and Vista, machines with single drives, raided systems and even a machine with a solid state drive. All have 2GB of RAM or more.
View 9 Replies
View Related
Mar 18, 2014
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
View 1 Replies
View Related
Jul 4, 2014
I am using Excel 2007 on Windows 7 Home Premium 64bit.
My Workbook contains 2 Worksheets, both with the same headings in row 1. Sheet2 is initially empty, except for the headings in row 1.
The following is a sample of the data in Sheet1:
[Code] ......
I want to create a Macro to operate as follows:
In the above sample data, rows 2, 4, and 8 are identical, so I want to copy rows 4 and 8 to Sheet2, and delete them from Sheet1.
Similarly:
Rows 3 and 9 are identical, so row 9 should move to Sheet2;
Rows 5 and 11 are identical, so row 11 should move to Sheet2.
Rows 6, 7, 10, and 12 are all unique, so should be unaffected by the Macro.
New records will be added to Sheet1 periodically, so the Macro will be used each time to move any newly discovered duplicates to Sheet2.
The Macro should make no assumption about how the rows are ordered, and should not change their order.
I have uploaded two Workbooks, such that Workbook1 shows the original data, and Workbook2 shows the result I want to obtain.
Attached Files :
Workbook1.xlsm
Workbook2.xlsm
View 14 Replies
View Related
May 2, 2012
I'm working in Excel 2007 and need to move data from multiple rows to a single row if the ID matches.Below is sample data I would be working with. I want to move data from columns F-U to the right of the original data in the row above it. I would also like to delete the rows that had data moved.
GIDSurnameNameEmployee Number OriginalDate of birthGranting ARE
Employing ARECountry Employing ARECHCM Supplier IDVehicle
Investmt. shares / Awards at termination dateMatching Shares
at termination dateTermination
[Code]....
View 3 Replies
View Related
Jun 4, 2008
I need to look into a particular cell on a specific column.
if this cell does not contain a number, then I need to delete the entire row, not column.
and then of course do this for 30K+ rows.
if sheets("sheet1").cells(x,7) is not a number ( dont know how to write this )
then delete row and move up.
View 9 Replies
View Related
Apr 2, 2009
I have a table A to J, column A, C, E are auto-populated with data in corresponding cells in another worksheet.
I need to automatically delete an entire row if; A1 (and on down column A)="Void"
I need to be able to specify which worksheet this is to run since I have 4 total worksheets where if "Void" pops in colum A, the row(s) need to be deleted.
View 5 Replies
View Related
Sep 15, 2010
I have a list of Cell Styles under the Styles tab in Excel 2007. I want to delete all the Cell Styles since its slowing down opening and operating of the Excel file. I do not want to individually delete them by right click-delete. Cmarco to run for performing this action?
***I DO NOT WANT TO REMOVE CELL STYLES FROM A RANGE- I WANT DELETE THE CELL STYLES FORM THE STYLES TAB IN THE HOME RIBBON IN EXCEL 2007****
View 1 Replies
View Related
Jul 23, 2014
I got the following script from another site. What it is intended to do is that it has three columns in a sheet.
Column A = Folder Name
Column B = Sender Name or Mail Subject
Column C = Values for Column B
Based on what values are in Column C (sender name or email subject), it will then look at the mails in outlook within the 'inbox' folder and look for the matching criteria and if it finds a match, move it to the folder named in column A (if folder does not exist, create folder)
Running the script, I get runtime error '438' object does not know property or method - on the line highlighted in red. I am using office 2007 and looking at the code, it is using late binding so no reference library is needed?
Code:
Option Explicit
Const olFolderInbox = 6
Sub moveOutlookMails()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
[Code] .......
View 2 Replies
View Related
Jun 20, 2014
I am having trouble delete a drop down list inspite of selecting clear all from the data validation tab in excel 2007. When I hit Alt + Down Arrow, I still see picklist options. I didnt set up the spreadsheet, hence I am not sure how to begin troubleshooting.
View 1 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Apr 15, 2011
In versions prior to Excel 2007, I was always able to copy a chart when I copied a tab. However, when I copy a tab in 2007, the data copies fine, but the chart does not. Something similar happens when I try to move a tab with a chart on it to another workbook.
View 5 Replies
View Related
Nov 10, 2012
i am totally new in excel and i am using excel 2007, my question is : how can i create an object in an excel sheet, so that when the mouse cursor move to it, it pops up anohter image?
View 4 Replies
View Related
Apr 9, 2014
My OS is Windows 7. My version of Excel is 2007. I have read how to get a macro to move around a page in a relative way, but is there a way to move from page to page in a relative way as well?
View 5 Replies
View Related
Jul 17, 2014
formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.
What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.
See annexed file for expected results.I have preference to have a formula much more.
View 7 Replies
View Related
Jun 6, 2014
I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..
I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...
What would be the best way to accomplish this?
View 4 Replies
View Related
Apr 5, 2012
I have a document that has 634 rows, some rows with data in red and some rows with data in black. I only want the rows with data in red. How do I get rid of all the rows with data in black all at once? I don't want to go through the document line by line.
View 2 Replies
View Related
May 21, 2012
I'm trying to delete a range of rows based on the value of a given cell. For example, if cell AC1 shows 5000, then I'd like to delete (not just clear) the range A5000:AA1048576. Or, if cell AC1 shows 10000, then I'd like to delete range A10000:AA1048576. I've come up with what is below but I don't think this is the right way to do it. I am using Excel 2010.
Code:
Range("AC1").Select
ActiveCell.FormulaR1C1 = "=COUNT(C[-27])+2"
Calculate
Range("AC1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A" & Range("AC1").Value:"AA1048576").Select
Selection.Delete
View 5 Replies
View Related
Mar 20, 2014
I have a company with upto 5 products, and I have created a dropdown box where you select your product.
When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.
I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.
View 4 Replies
View Related
Sep 10, 2012
I'm new to this forum and to VBA
First-off, I'm using Excel 2003 SP3.
The setup: A software application I regularly use produces csv data files (in this case approx 300 files). These I have converted to xls format using a separate macro. The files are in one folder and named as follows eg
G1 18800.xls
G1 18802.xls
G2 18975.xls
G3 19881.xls
G3 19990.xls
G5 19990.xls
etc
The files contain the following data:
DATE TIME VALUE STEPS EXCL ACCEL
01-Oct-03 08:00 0 0 FALSE 0
01-Oct-03 08:01 0 0 FALSE 0
01-Oct-03 08:02 0 0 FALSE 0
01-Oct-03 08:03 0 0 FALSE 0
01-Oct-03 08:04 757 20 FALSE 0.18
01-Oct-03 08:05 1714 32 FALSE 0.44
01-Oct-03 08:06 1524 32 FALSE 0.39
01-Oct-03 08:07 1665 45 FALSE 0.47
01-Oct-03 08:08 1644 42 FALSE 0.46
01-Oct-03 08:09 263 8 FALSE 0.06
etc
I then created a macro using code I sourced from the internet, and included some addtional commands (filename, copy/paste). See below. This macro opens the all the xls data files in the folder and copies the relevant data to an analysis workbook [Analysis sheet, Results sheet, Master sheet] ie the data is copied from the data file and pasted into the Analysis worksheet. Then the results are copied from the Results sheet to the Master sheet. So far so everything works.
My problem is this: I'm stumped at how to delete rows from the xls data files before running the analysis workbook and macro. In other words, after converting the csv files to xls format I need to clean the xls data files.
I have a separate xls file with criteria data in two columns:
FileName Date
G1 18800 06-Oct-03
G1 18801 02-Oct-03
G1 18801 03-Oct-03
G1 18801 05-Oct-03
G2 18795 14-Oct-03
G2 18795 15-Oct-03
G2 18795 16-Oct-03
G2 18795 17-Oct-03
etc
The rows in a particular data file, with dates that are not present in the Criteria workbook must be deleted. This to be done for every data file in the folder. Also, I need to exclude rows for specific time periods eg 12H00AM - 04H00AM from all the files irrespective of date.
the code needed [should a separate macro be run or can code be placed within the present macro?]. I've tried looking for something similar on the internet, but my requirements seem too customized to be able to adapt the code that I found. And of course this is waaaay above my present skill level!
Option Explicit
Sub CopyPaste()
'This code opens up data files (xls) in a specified folder and copies data A1:G17281 to an Analysis workbook (Analysis sheet).
'The data from the Results sheet is then copied to the Master sheet.
Dim wkbDest As Workbook
Dim wksDest As Worksheet
Dim wkbSource As Workbook
Dim wksSource As Worksheet
Dim MyPath As String
Dim MyFile As String
[Code]...
View 9 Replies
View Related
Dec 13, 2011
I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
Here is what I currently have:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS
Here is what I need it to look like:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
View 3 Replies
View Related
Feb 16, 2014
how can I write a routine to add rows in one Sheet if a condition which involves a different Sheet is met (excel 2007).
In Sheet 1 is a list of ("liquid") names (cells C4:C26) which will need to be expanded should the IF condition be satisfied. In row 27 I have a subtotal (whihc is using data from columns D onwards), and from row 29 down cells are not empty.
The condition I want to verify is in Sheet 4-column I, where it is reported whether a name is classified as "liquid" or "illiquid". If the number of "liquid" names in sheet4-column I is more than the number of names in Sheet1-C4:C26 I would like to add as many rows as the difference between the two lists. Also I would like to make sure that the subtotals that I have in row 27 (and that will be shifted down when new rows are added) will also inlcude the data in the new rows (the subtotal is taking data from columns D onwards).
Should the code need to inlcude the name of the tabs, Sheet 1 is called Summary and Sheet 4 CDS Data.
View 1 Replies
View Related
Aug 13, 2013
I have a large file with a bunch of color coding and need to delete my red rows only.
View 3 Replies
View Related
Jan 10, 2012
I have a list of about 4,000 contacts each with about 2 or 3 products with us. Each product creates a new contact row.
I would like to merge each row with the same contact into one row.
I have attached an example : Example test.xlsx
View 14 Replies
View Related
Jan 19, 2014
Trying to freeze top 2 rows as headings.
I select top two rows then
Under VIEW / FREEZE FRAMES I select Freeze ... according to current selection.
But what freezes are rows 1-15 and colums A-N, which I never selected.
How do I get Excel to freeze just the top 2 rows?
View 2 Replies
View Related
Jan 24, 2012
I have files that have thousands of rows in them with company names. The rows contain the company name plus lots of other info. I'd like to be able to create files or sheets with the company name as the file or sheet name with the respective rows contained within it. Make sense?
For example:
Original file or sheet
Date Company Info1 Info2
Jul Sams Expense Desk
Jan Freds Expense Doughnuts
Jun Sams Deposit Return
Feb Sams Expense Food
Dec Freds Expense TV
Would become:
Sheet or File Sams
Date Company Info1 Info2
Jul Sams Expense Desk
Jun Sams Deposit Return
Feb Sams Expense Food
Sheet or File Freds
Date Company Info1 Info2
Jan Freds Expense Doughnuts
Dec Freds Expense TV
View 6 Replies
View Related
Feb 27, 2012
I have a spread sheet whereby it contains:
- 12 unique values in column 6 which represent states within the country.
- 7 unique values in column 25 which represent the types of services.
- 5 unique values in column 27 which represent the days for doing the services.
I also have a user form that contains CheckBoxes equivalent to all above unique values, and I want the CheckBoxes to do the duty of a filter, whereby after selecting the needed CheckBoxes and clicking on a seperate botton "creating report", I want to show on the spread sheet only the rows of the values being checked and all other rows which don't contain the needed values should be either hidden or deleted.
I'm using Microsoft 2007 on Windows 7.
View 2 Replies
View Related
Apr 22, 2012
Excel 2007 holds data of mine in a "format as table" Table. I want to rank the values in one column. Normally I want to do that by ranking the value in the current row against all the remaining values in that column. That is easy.
I got stuck today when I wanted to rank the value of the field in the current row with only a subset of the values in the column. I want to rank the current against the half-dozen or so records that have "A" in an adjacent column, and rank values having "B" in that other column against only the other values a "B" and so forth.
It would be best for all this to be done in formulas in the table rather than a clever use of a pivot or other? But really, I may be open to different approach than I was trying for if I can use successfully!
View 2 Replies
View Related
Oct 29, 2012
Is there a way to select completely empty rows in Excel 2007 (Win 7)? I have found directions for highlighting empty cells (f5, Special, Blanks...), but sometimes the cells selected will be in rows with other filled cells in them so that doesn't really do what I need.
View 7 Replies
View Related
Jul 29, 2013
I am wanting to find rows that with unique values in the first column, and then take those rows and display them in a more readable format. The section titled input is an example of information that would be pulled from a query into Excel. The Desired Output section is how I would like the info to be displayed. If this is possible without VBA then that is even better.
Excel 2007
A
B
C
D
[Code].....
View 2 Replies
View Related
