Delete Columns, Increment Alphanumeric Text, Insert Rows & Sort

Feb 18, 2008

I am having to write a vba code for a survey data captured code online.

I am attaching the code and the vba module that is suupose to do

following things

2) dELETES THE LAST 3 COLUMNS

3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)

4)Inserts new row at top from Q1 TO Q11

5) Sorts data on Q6 (Faculty/Staff)

Before all this the i need to insert blank cells for questions that have not been answered and shift the cells right (condition 1) ....

View 9 Replies


ADVERTISEMENT

Increment Number Within Alphanumeric Text

Apr 4, 2008

Is it possible to increment / decrement cells using wild characters? I've found myself needing to increment values at different position, for e.g. :

ABC-###-FR05-AB
ABC-222-FR##-AB
C##445-KJ

where I would want to be incrementing # placeholders. It would help if I can give it a start number, and the increment amount (which can be different than 1).

What about possibly extending it a bit more to include two or more place holders at the same time as follows:

ABC##- FR*** (where # would have its own starting number and increment value, and * would have its own).

View 9 Replies View Related

Increment Numeric Part Of Alphanumeric Text

Aug 16, 2007

I have a cell with a value of, Text 1 and in the next field I want it to display Text 2, then text 3 and so on.

Is there any way of doing this? I guess I am looking for something like a1+1, just a shame it doesn't work.

View 9 Replies View Related

Compare Columns & Insert / Delete Rows

Feb 22, 2008

I have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.

There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.

View 9 Replies View Related

Sort AlphaNumeric Text By Numbers

Dec 6, 2006

Take for example ABC10 in Cell A1, ABC199 in cell A2 and ABC9 in cell A3

How am i able to sort such that ABC9 appears in cell A1 followed by ABC10 in cell A2 and ABC199 in cell A3?

View 9 Replies View Related

VBA Macro To Delete Rows/Columns, Insert Formulas & Auto Fill

May 28, 2009

I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.

Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"

UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................

View 2 Replies View Related

Move And Sort With One Column But Insert Extra Columns As Needed For Proper Sort?

Jan 13, 2014

Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.

Edit: Not sure what happened but file was NOT understandable before. It should be correct now.

View 2 Replies View Related

Alphanumeric Increment

Jun 18, 2009

I would like to have a drop down list for cells in a row - say B1:I1.

Firstly, having the list (with only 2 selections) in these cells to place either an "A" or a "0" (zero) in a cell.

Next, if an "A" is added then a selection of a "B" or a "0".

If a "B" is added then a selection of a "C" or a "0". etc etc

If the "0" (zero) is selected and placed in the row then no more alpha but just the next number "2" available to select only then a "3" etc etc.

These would be added from left to right ie: A to H or 1 to 8.

I might be able to do this with a long formula but problem is there are 100's of rows. Maybe VBA would be more efficient?

View 9 Replies View Related

Replicate And Increment Alphanumeric Keywords?

Dec 15, 2007

I want to create a list from a "seed" word that consists of a word followed by a number (ex: seedword100).

What I want to do is to be able to create a list in Excel using that seed word and then have excel produce a bunch of replications of that seed word by incrementing the number part of the seed word by 1 for every replication.

So, for example, if cell A1 = "seedword100", I want Excel to replicate it up to "seedword199" like this...

B1 = seedword101
C1 = seedword102
D1 = seedword103
E1 = seedword104
F1 = seedword105
G1 = seedword106
H1 = seedword107 ...

and so on until the last cell = seedword199 (or whatever number I want it so stop at).

View 7 Replies View Related

Excel 2010 :: Increment Alphanumeric Value

May 1, 2012

Code for incrementing alphanumeric values as per details below. I am using Excel 2010 on Windows 7 (64 bit)

I have data as under in cell A1 "SOC-2012-00001001"

When I press enter I want the value in cell A2 to be incremented by 1 eg A2 "SOC-2012-00001002"

Similary on pressing enter A3 should show SOC-2012-00001003 and so on.

View 9 Replies View Related

Increment Numeric Value In Alphanumeric String

Dec 31, 2007

I have created a macro that searches for an existing part in my worksheet and copies the found part to the row above. I would like to increment the value of cells B and H in the new copied row. Both cells contain an alphanumeric string that ends with a numeric value. For example cell B has a string like this "APL-DK0030" and cell H has a string like this "Dell Optiplex GX260 V09"....

View 9 Replies View Related

Insert Blank Rows, Sort And Highlight

Jul 31, 2009

I am trying to accomplish three things with the attached workbook. The first thing is the insert a blank row between the different part numbers, column "A" to make the sheet easier to read. The second thing is to highlight the data in light gray (A:E) leaving the blank row that was created empty. The third is to then sort the worksheet by data in column "A" to group like numbers together.

View 12 Replies View Related

Macro To Delete Rows And Sort

Sep 27, 2009

I have attached a worksheet to illustrate a "Before" (raw data) and "After" (desired results).

Each row contains data on a forex trade. The variables which must be assessed in each row are:

Currency Pair (e.g. GBP/USD).
Opening Time
Closing Time

What I need to do is delete many rows in the raw data because they reflect a condition I do not want in my analysis, which is having multiple open trades of a single currency pair during the same time period.

To perform my analysis I need to delete all trades (rows) which meet the above condition.

The worksheet provides examples, along with a rudimentary tool I've used to help me sort/delete rows manually.

Since I am looking at thousands of trades, a manual sort/delete is impractical. (my eyes don't like this kind of work!)

The rudimentary tool I've used in manual sorts/deletes is simply to have a column which returns a "1" if a trade opens before an older trade has closed. I do this by first sorting the data by (a) currency pair then (b) date opened. Then I must manually go through multiple iterations of deleting trades until there all of the "1's" have disappeared.

I'm hoping someone can show me a macro which might do this sorting/testing/deleting automatically, at least to the point where all I have to do is repeatedly press a "macro" button until there are no "1's"

View 8 Replies View Related

Sort Text By Character Length, Insert Spaces, Merge Cells

Apr 19, 2007

I have multiple columns with many rows of unique text in each. Here is an example of what the 1st few rows of column a and b might look like:

A___________B
hboc______dds
jk________optg
hbv_______pl

FIRST:
I would like to be able to sort the rows by the character length in a column. So, it would look like this (if sorted by 1st column):

A___________B
jk_________optg
hbv________pl
hboc_______dds

Second.........

View 2 Replies View Related

Insert / Delete Rows

Jul 6, 2009

I'd like to create a variable list. If a number is entered into B7 (see attachement), I would like that number of rows being inserted below. Starting at A8. But I would like the list to be variable. So if a lower number is entered, the rows would be deleted. And I would be great if the formulas could be maintained.

View 5 Replies View Related

Insert And Delete Rows With VBA

Jun 6, 2014

I've a protected sheet with one table with 6 columns and 1 row (to be filled), and the column headers.

I want to allow the user that will fill the sheet to insert more rows with a button (+) I want also to allow him to delete the last row with a button (-), except the first row, because the table must have at least one row.

How can I do this with vba code?

View 1 Replies View Related

VBA Delete Rows And Insert Formulas

Jun 23, 2013

delete rows of a report which contains $- or zero value but not row with Beginning Balance. Data begins from Column B to Column E. Some records may need to be deleted completely from Account Number down to Subtotal rows plus one empty row, if the Subtotal amount is $- or zero. Below is the sample data and how the finished sheet should look like. The report will comes in thousand of line. I don't have knowledge in programing but believe it can be done through VBA.

Account Number

211-00-5936-00-001-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount

[code]....

if Subtotal is $0, then delete the row starting from Account Number to Subtotal plus one empty row

211-00-5936-00-002-3-00-2-00
Trans. Date
Doc. No.
Trans. Line Comment
Actual Amount

[code]....

View 2 Replies View Related

VBA To Insert And Delete Multiple Rows

May 30, 2009

Does anyone have codes to insert and delete multiple rows. I need to run a macro where a dialog box pops up requesting number of rows to insert and delete.

View 9 Replies View Related

Checkbox To Delete Or Insert Rows

Mar 23, 2007

I have a worksheet that has been created to essentially request information needed to determine a demo system configuration. Within this worksheet there are a series of checkboxes that if checked, will need to have the form extended (i.e. add additional questions to clarify say a model or manufacturer). For example, if the Yes checkbox is checked next to a question I want a macro to copy some rows from a hidden worksheet within the same workbook to the bottom of the non-hidden worksheet. If the No checkbox is clicked, nothing should happen. Furthermore, if a user accidently click Yes but didn't mean to I want the added rows to be removed. This is further complicated by the fact that there are 2 or 3 questions that have check boxes that can add additional questions.

View 9 Replies View Related

Delete & Merge Columns,Delete Rows With Filter, Etc

Jul 15, 2009

1. Remove J,K,N,A Columns,

2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy

3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value

4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only

5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )

View 3 Replies View Related

Automatically Sort A Workbook On Opening And Delete Blank Rows?

Jul 4, 2013

I am trying to educate myself in the wonders of Excel (2003) and an struggling somewhat.I have a small project loosely based on a shops stock control and have attached a copy of the workbook -

The user normally only has access to columns B, C and E due to the others being locked and the sheet protected.I would like the sheet to be automatically sorted based on column E each time the workbook is opened.I then need to automatically delete a row once an item in column B has been removed (made blank)

View 3 Replies View Related

Allow Users To Insert / Delete Rows On A Protected Worksheet?

Apr 5, 2014

I have a worksheet that I have some "locked" cells on. I also have protected the worksheet but when I check to allow users to insert rows and to delete rows and then save it, when I re-open it I can't insert rows or delete rows???

I am using some VBA to allow for the collapseing of rows while maintaining protection and that works perfectly. I want users to be able to insert and delete rows while maintaining protection. I thought a simple check in the protection was good enough, but for some reason it isn't.

View 2 Replies View Related

Macro To Extract/insert Data Then Delete Rows

Jul 25, 2008

I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.

******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3  Transactions for  CUSTOMER ID: 0000CUST7   Sales        4        5        6Product summary for Vehicle ID   00001080       7        8Product Description  Transactions Quantity9        101 Unleaded  3 57.60  GL11        12Hose summary for Vehicle ID 00001080       13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60  GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


I am trying to get the data into a more convenient format for analysis. I need a macro that will:

1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).

2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.

******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded  3 57.60  GL111081 Unleaded  6 84.70  GL121122 Unleaded  5 47.00  GL131182 Unleaded  8 95.80  GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Sort Rows Depending On Value In Only One Of Columns

Aug 16, 2012

I wish to sort all of my rows depending on the value in only one of the columns. I do not know how to set this up, my data starts in row 7 and is in columns B:F, needs to be sorted by descending in column B.

View 1 Replies View Related

Sort Text To Certain Columns

May 30, 2008

What formula will sort the infomation in the first column into the other relevant columns.

View 9 Replies View Related

Sort Alphanumeric Date From Recent To Oldest?

Aug 19, 2012

How do we sort an alpha numeric date from recent to oldest.

from this:-
HGH010112
HGH231211
HGH020212

to this:-
HGH231211
HGH010112
HGH020212

View 2 Replies View Related

Insert Rows Based On User Input But Then Auto Delete Them At The End

May 25, 2013

I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.

The code I have for the inserting the rows (from searching through forums) is:

Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9

[code]....

View 1 Replies View Related

Sort Repetitive Data From Rows To Columns

Jun 23, 2006

I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?

View 9 Replies View Related

Columns To Insert The 2 Rows

Aug 22, 2007

I have this excel file which has data in it. However, this data will come in everyday. Eg, A1 to A10 is QWE, A11 to A20 is RTY, A21 to 30 is UIO. But as I said earlier new data will come in everyday. For eg, it will become A1 to A15 is QWE, A16 to A30 is RTY and so and so forth.

I need to insert 2 rows after QWE, RTY, UIO. But as data will come in everyday, I cant standardise my columns to insert the 2 rows.

View 14 Replies View Related

Sort Column Of Variable Length Alphanumeric Characters?

Aug 2, 2012

I'm trying to sort by a column with one to four digit numbers, any of which may be followed by a single letter, i.e. 1,2,3 4A,5, 10,11A,75,101A,600,705,1010B,1011A.

I'd like them to sort in the above order. I am only able to have excel 2003 sort all of the numeric cells first, followed by the cells with the alpha character.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved