i want to hide rows in accordance with the line numbers that have been determined with Macro, on G3 (Start Rows) and H3 (End Rows).
And i have attached my example file on excel (.xls)
The best way to explain my problem is to look at the table below:
How it looks now: ApplePrice 1
Price 2
Price 3FruitDeliciousPearStore 1
Store 2FruitVery DeliciousHow I want it to look:ApplePrice 1FruitDeliciousApplePrice 2FruitDeliciousApplePrice 3FruitDeliciousPearStore 1FruitVery DeliciousPearStore 2FruitVery Delicious
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedI have a huge set of data values given per 0,5 hour. Now I want to make a chart which shows how many hours each value has taken place.
Here is what it looks like with the arranged data as y-values. Instead of the x-axis showing the number of values, I would like it to show the above mentioned.
How to do what I am about to ask takes time and time causing brain overloading. I have made screenshots but I can't upload them. I get red exclamation point with images sizing ~55KB
View 9 Replies View RelatedIn excel I would like to create a macro which wil be able to hide all lines were a text (to be defined in a cell for example) is not present
Here is an example:
linesdata:X#1#2a#3b#4b#5a#6a#7c
Pending X value lines #2 to #7 will be hidden or not
The following code adds a new row at a given point (Based on a fomula in a column returning "1")
The code works well BUT I want to merge this new row from column A to coulmn M
Im not sure how to do this, (I dont want to merge the entire row)
Example Code:
Sub ExtLne()
Dim Lastrow As Long
Dim I As Long
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
I = 1
While I
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
When using 2013 Excel in the Home tab, line 1 is not visible. Using the File tab will show line one, but the Home title bar is unavailable. Toggling back and forth is not efficient. No one in this office has ever seen an Excel program not display a full page under the title bar in the Home tab.
View 1 Replies View RelatedI am trying to figure out how to build a drop-down menu with VBA that has 2 built in choices, 'call' or 'put', and based on the selection in the drop-down list (call or put), the value in the cell directly to its left will be 1 (if 'call' is selected) or 2 (if 'put' is selected).
I have tried doing this with the data validation tool, but the problem I have run into is that I do not want to store the items in the drop-down list (call or put) anywhere in the excel sheet.
I am working in cell T3 of 'workbook A'.
I want to lookup a value from cell C4 in 'workbook B' if the value in cell G3 of 'workbook A' equals "Yes". If the value of cell G3 in 'Workbook A' equals "No" I need the value to be 0.
I am trying to get my User Form to work whereby I choose a pre-determined date entry. I have tried some coding but cannot get it to work. What code I need to add in VBA for this to work? I have tried to alter the time entry from my previous post that was solved but are doing something wrong.
View 11 Replies View RelatedI have a row stored in the variable Found_Row3 and I want to set the variable ACRow to all colomns from A to Z of that row. Below is what I have so far...not working
[Code] .....
Is there an easy way of counting odd or even numbers in a row? I can't figure it out on countif, isodd, odd, count etc.
View 2 Replies View RelatedI have a line of numbers in one cell (B2). They look like this;
1, 21, 21, 94, 64, 2, 213
These numbers are currently in Decimal format (more on that later).
I need to "split" these numbers into individual cells and drop the comma. It would look like this;
B3 B4 B5 B6 B7 B8 B9
1 21 21 94 64 2 213
I know I can use the "text to column" function but I was hoping for a VB script to do this.
The second part is I then need to convert the numbers from decimal to Hex and add a "0" to the front of any single digit numbers.
I can do this part using standard excel formulas with no problems but if it could be added to the script then that would be great.
The final thing needs to look like this;
B3 B4 B5 B6 B7 B8 B9
01 15 15 5E 40 02 D5
1) I work with dozen's of tabs within workbooks, adding rows (that are numbered) etc. When I'm done and want to save the file, I usually go through this routine to make sure I'm at the top of each page before closing the file:
Ctrl-Home, Ctrl-Page Up, Ctrl-Home, Ctrl-Page Up ...through all tabs.
2) I also have to "add" all the extra line numbers (by dragging the last number I had in "row A", and filling down). I need to add the newly added rows to the "print area". Any easy way to do that?
Incrementing line numbers is a simple task.
If line Identification starts with
AA
AB
AC, why doesn't the same rules apply?
Highlight the cells then click and drag the corner down.
I need to distribute a list of data into 10 even groups of data. Here is the scenario.
I have 10 salespeople on my team with a list of accounts that need to be evenly distributed based on two criteria
- Each sales person needs to have the same number of zipcodes , with the closest TOTAL # of accounts and TOTAL $ value as possible
I have a list of dates going through several years of data - month ends and starts don't always coincide with calendar months. I need to figure out a way to get the oldest date of the previous month, for example. Tried vlookup but could figure out how to do it for one year only, hence not as applicable with several years of data.
View 9 Replies View RelatedCol AB has numbers from xero to 110 in descending order. I need a macro that will open a blank line between the numbers 29 and 30.
View 9 Replies View RelatedIf I number my lines of code and an error occurs, the use of Erl in the VBA will then return the exact VBA line number that the error occurred on. Is it possible to automatically capture or record that same effect when the macro process leaves its current sub and goes to another?
Reason: I'm trying to create a Call Stack that can be reported through an Error Handler that will include the exact location and process that the error occurred to better troubleshoot and understand where and why the error occurred.
The best I can come up with so far is manually putting in bookmarks along way so I know how far along the macro went before the error. From all of my searching I believe retrieving the Excel Call Stack is not possible and so one must be manually created.
Enclosed is an example of what I have so far. It goes through several macros and logs the Call Stack. It’s a work in progress so it is a little sloppy looking but it is functional. If a Sub finishes it is then taken out of the Call stack.
Several "BookMarks" are placed to give an idea of how far along the Macro has gone within that Sub. The Code for the Erl example is:
Sub SampleErrorWithLineNumbers()
Dim dblNum As Double
10 On Error Goto PROC_ERR
' Errors if table doesn't exist
20 Select Case Rnd()
Case Is < 0.2
30 dblNum = 5 / 0
40 Case Is < 0.4
50 dblNum = 5 / 0
60 Case Is < 0.6
70 dblNum = 5 / 0
80 Case Is < 0.8
90 dblNum = 5 / 0
100 Case Else
End Select
110 Exit Sub
PROC_ERR:
120 MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _
"Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub
I have a source data sheet that has around 50 columns. I want to copy and past selected columns to a destination sheet.
View 1 Replies View RelatedI need to create random numbers with 10 columns and 10 rows.
But for each line can not be the number opposite each decade.
79
22
48
70
04
98
09
41
32
56
[Code] ........
see the first line was generated as the number 79, then this same line, can not generate the number 97.
number 22 in this case is OK.
The first line was generated the number 84, then this is not the same line, you can generate the number 48.
The first line was generated the number 07, then this is not the same line, you can generate the number 70.
and so
97
22
84
07
40
98
90
14
23
65
I have several thousands lines of data....much of the data is the same, 2-5 rows per person, but at the last two columns is different numbers/totals....I'd like to get those all into their own column so that each person has one row with all the data...I USUALLY would sit and sort by each total, shift them over to the right into their own columns, then sit and shift them all up to one line...but thats a carpal tunnel project and i know there must be an easier way....subtotalling brings the numbers down to one line once I have shifted each one over, but not the rest of the data....
Pic Attached: Excel problem pic.JPG
See the code below. It works fine but in the report that gets printed off, it doesn't display grid lines and line numbers .
[Code] .........
I am looking for a time saver macro,pretty easy to make i guess,as the theory is not difficult...but i am too newbie to make it. So i have an xls that has like 20k lines on Column A!And i have to seperate the numbers. I count the first 4 digits and I have to do it by adding a cell between them.
Example :
27289802
27289902
27289915
27289915
(add a null line)
27290202
27290302
27290316..................
I have thousands of lines of data that are of the following form:
Jackson, TN 9623 BCBS TN 98 UnitedHlthCare 2
Jacksonville, FL 3577 Aetna 47 BCBS FL 37
Knoxville, TN 3796 Cariten 44 John Deere 41
Lakeland-Winter Haven, FL 2165 UnitedHlthCare 31 Aetna 29
I need to separate these lines into columns such that, for example, the first line would be in 6 columns: (Jackson, TN) (9623) (BCBS TN) (98) (UnitedHlthCare) (2)
The delimiter does not work because some of there is no one character that always separates the text and the numbers. I've tried going through in word and typing something like '%' where I want to separate, but with thousands of lines of data that is extremely tedious.
Is there any formula I can use that would be capable of solving this?
I download a daily inventory file from my supplier.
.
I sell only selected products from that file.
.
I would like to use a list of the product part numbers of the products I sell on sheet 2 (Inventory) and have all the results from sheet 1 (ProductFeed) built into an expandable table on sheet two including all the parts numbers (expandable because I will add or delete part numbers from the list) I have listed and not just one part number but several.
.
Second issue: the download file contains part numbers in column "A" with variables like 418, 418_color, 418_size etc.... I would like all part numbers that begin with the number 418 and the ones that contain the variable added to be listed in the new table so I will get a list of all part number 418 and the variables which include 418 in them.
.
I want to return the results of the whole row(s) of the matched data as well in the new table.
.
I have attached a file of the working data that I have with the data I will use although the ProductFeed will change daily which I intend to copy and paste into the table each day.
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.