I have VBA code which selects a group of rows ranging between 1 row and 25 rows. I then want code to group the selected rows, however, the only code I know for grouping requires that I enter a specific starting and ending row (ExecuteExcel4Macro "SHOW.DETAIL(1,#,FALSE,#)").
Is there a way to have VBA group and hide a number or rows which is not known in advance and will change between daily executions of the code?
The following code inserts a row below the selected row, and copies the formula of the row above into it.
Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
I am having a problem with a VBA macro I am trying to write. All I want to do is check all rows in a worksheet and group rows from the last bold row to the next bold row.
I seem to have the conditional logic correct, however, instead of grouping rows separately in each loop, it keeps joining them together.
I have a grouping and subgrouping of rows in my worksheet and I want to use the minimum value from each group and subgroup and use those values in a report on a separate worksheet using vba. Here's the situation
Emp List (Column)__________________________ Salary
- Workers
__+ Foremen
__- Juniors
____John__________________________________2000
____Max___________________________________1800
____Sandra_________________________________1925
- Management
__+ Seniors
__+ Middle
Lets say Max has the lowest salary among all the workers then I want the report to look like this
Emp Group__________ Emp____________________ Min Salary
Workers____________ Max____________________ 1800
Can I do this using vba without multiple loops at all the group levels?
I have a permutation with repeatition of 3 letters in 9 digits 3^9, and I'd like to be able to group all posibble permutations something like:
BBBBBB+2+1 = BBBBBBCCD = 252 rows
BBBB +3+2 = BBBBCCCDD = 1260 rows
5+4
5+3+1 and so on
I have a spreadsheet that requires grouping at two levels. See pasted image below.
I need a macro that on the first pass dynamically creates groups of different sizes of data based on values of one column. For example, a group is created for the RR's, a group is created for the SS's, and a group is created for TT's.
On the second pass I would like to group the groups based on the values of another column for the 2nd level of grouping. So the A's would be one group and the B's would be the other.
TotalAAToTalRRARRARRARRAToTalSSASSASSASSATotalTTATTATTATTATotalBBTotalUBUBUBTotalVBVBVBVBVBTotalWBWBWBWB
I can have 2 tables set up for the values that I need grouped at each level.
Table 1Table 2Group 1Group 2ARRBSSCTTUVWXY
I had recorded a macro where I manually grouped the data. However, this did not help, because I am creating a template that needs to dynamcally handle the data that comes in.
I have a pivot table (vendor spend report) which has over 7000 rows of data. There are 38 vendors (some vendors appear multiple times i.e. Accenture Itay, Accenture Germany, etc.). Vendors names are in column B.
I need to have the VBA script search for each of the vendors (and related names) and group them together. Also, last one is for all the MISC. vendors which need to be group together (one's that dont belong to the 38 managed vendors).
Also, I need to rename the Group for each one; rather than Group1, I need to name it "Accenture All" etc...
I have a report that shows 3 lines for each item. Is there a way to group sets of 3 rows together so they print on the same page so that it would insert a page break either before or after each set of 3 rows, not between them.
View 9 Replies View RelatedI need to totally ungroup existing grouping of rows in a sheet. Totally ungroup = strip it totally of any grouping. In short, it should be back to its original state of no grouping at all.
Problem is that I do not know if the sheet has existing grouping, or if it does, how many levels of grouping.
The solution I have in mind right now is just to indiscriminately run ungrouping vba line 10X and just place an error handler i.e.
Code:
Sub Macro1()
On Error Resume Next
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
Range("A5:A29").Rows.Ungroup
[code].....
Is there a shorter way to handle this code-wise?
What I have just tried is click "Reset" in the macro menu and resaved the macro and the workbook, and the proper max/min lines seem to be working. However, I still feel a little unstable about it and will watch it closely.
(My next questions is how to "freeze pane" a row of headings on the results page and post the results onto row 2 and downward *without* deleted row 1)
Hello,
Here is my hurdle.
Could someone please look at the code below and see if there is a hint why all rows from the top all the way down (from R5 to R604) are outlined, when the code should successfully be stating already that if a value in the S column (to the right) is the maximum, then only outline the rows that across the R rows (up and down) that are the MIN? Then, those red-outlined rows should successfully display on the Results page.
It worked before, but I do not know now why all of the rows on the Details page are now selected.
Is there some indicator in the code that says outline everything?
Issue 2:
When I try to run the macro from a button I created, a pop up message says that the file is already open. If I take that literally, it is indeed true because I want the file open. Apparently Excel thinks I have 2 files open with the same title. It wants me to either close one of them or rename them, according to the message. But there isn't another file open according to my searching. What does that message really hint for me to do?
I have the following code, to find the 1st cell in column CG that contains a value of more than -200. I wish to delete the entire row that this resides on, and all rows below.
Range("CG2").Select
Do While Not Selection.Value > -200
Selection.Offset(1, 0).Select
Loop
I'm trying to write a macro that if a 1 appears in column N (1 may appear more than once & the sheet continues forever) then to hide that particular row.
I will also then have a seperate macro to delete the entire rows where 1 appears.
This works fine in detecting and copying the new material number accross to the master data sheet, but now i want it to copy the row in which the new material number is located as there is other information that goes with so it doesn't have to be manually typed in.
Dim MyValue As Variant
Dim FromSheet As Worksheet
Dim LookupColumn As Integer
Dim FromRow As Long
Dim FromColumn As Integer
'-
Dim ToSheet As Worksheet
Dim StartRow As Long
Dim LastRow As Long
Dim ActiveColumn As Integer
Dim ReturnColumnNumber
Dim ToRow As Long
Dim FoundCell As Object
'================================================= ============
'- MAIN ROUTINE
'================================================= ============
Sub DO_LOOKUP()
Application.Calculation = xlCalculationManual
'----------------------------------------------------------
'- LOOKUP SHEET [**AMEND AS REQUIRED**]
Set FromSheet = Workbooks("Book1.xls").Worksheets("MD")
LookupColumn = 2 ' look for match here
FromColumn = 2 ' return value from here
'-----------------------------------------------------------
'- ACTIVE SHEET
Set ToSheet = ActiveSheet
ActiveColumn = ActiveCell.Column................................
I think this might be a programming issue (not sure but I hope it can be solved).
I have three sheets, each containing a table. I'd like users to be able toggle or select rows from these tables for inclusion in a fourth table in a new worksheet.
Unfortunately I have zero knowledge of vba and 0.01 knowledge of macros. I'm attaching a sample of what I'm working with.
In the attached, the "price list," "likely to reference," and "optional" worksheets contain the source tables; I've added a column to the right of each called "include" (where I'd like a user to be able to select a desired row).
The "custom" worksheet is where I'd like an assembled worksheet to live.
I have a worksheet which contain a lot of data in it. I want only certain rows and columns to be printed when I click a button. Actually I am trying to print a report.
Suppose I have data across A10:M100. Lets say from columns A to D are permanent, if the cell colour of any cell from E to M is not Red then that is to be printed along with the contents in A to D. Since I have bunch of data in that sheet almost 10000 rows I separated data by giving them name. If it is possible to have an option to choose from before printing that would be much much better. Because even though there are many cells left uncoloured I don't want them to be in the print as they are belong to another table (I have given name for each table)
If all columns from E to M in row 20 are Red then that row should not appear in the print. If J20 is left uncoloured then it has to be printed along with the data from A20 to D20 ( which I said permanent). This colour I fill by a macro as when required only for the range E to M.
I have two workbooks say 'MASTER' & 'REVISED' in D:Office. The REVISED workbook contains certain rows colored in Yellow which contain the rectified data. How to copy those rows (rectified data rows of workbook REVISED) to the workbook MASTER in the same row number.
View 7 Replies View Relatedi would like to paste also the rows that selected from listbox i use a userform and also use multiselect listbox
Code:
Private Sub frmstartbtn_Click()
Dim lItem As Long
With Me.frmListBox1
[Code].....
I can use Find to find all all my rows where there is an "X" in a cell, and delete all the rows found that way (even if they are separated by other rows) in one fell swoop -MANUALLY. I use Find after having limited the area to be searched with a named array (so other "X's" don't get involved).
But when I record a macro with all the same moves, NONE of the Find code appears in the macro AT ALL....just the delete command. Hello? Relative reference (on the record macro toolbar) seems to have no impact.
So....the mission here is to delete entire rows wherever an "X" has been entered in a certain cell to mark the row for deletion...and those X's get there either through a DV list OR by a cell below the DV copying down the X from the DV cell above.
This is so because sometimes rows are "sub" to the one above, and if the one above is marked for deletion, then so must be the rows sub to it.
I have found this seemingly simple for...next loop here on the board:
For a = 1 To 50
If Cells(a, 17) = "x" Then
Rows(a).Select
Selection.EntireRow.Delete
Next a
But the debugger reports a "next" without a "for" which is obviously there in dark blue as I suppose it should be.
If I could make this puppy work, I would sooner have it start from A2 and then go down from there to the last row -wherever that happens to be.
I would like to select a number of columns and then run a macro that would insert two columns after each of the selected columns.
Or is there perhaps a faster way than using a macro?
I’ve got a little problem with the text to column function.
I’ve got in column A critarias and if it is for example L it should run a surtain text to column code.
With Range("A1:A65536")
. AutoFilter Field:=1, Criteria1:="L"
Range("B1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).TextToColumnS Destination:=Range("B2"), DataType:=xlFixedWidth, FieldInfo:= Array(Array(0, 1), Array(21, 1), Array(60, 1))
End With
With the Auto Filter some rows between 2 until 7624 are shown. But unfortunately the text to column code is used for every row between 2 and 7624. Does anyone know how to avoid it?
I need to copy only user selected rows ( multiple) from one sheet to another. I see similar threads here but none that work with user selected rows.
View 4 Replies View RelatedHow do you count know the numbers of rows that has a selection
View 2 Replies View RelatedThis is easy enough, but I only want to have items listed if every field on the row is unique which I can do, but is it possible to only display these unique lines and have any lines that contain a duplicate not be shown as a blank or anything else?
View 1 Replies View RelatedI have multiple departments and on each department we report the costs in four columns this month, budget, last month and last year. I have summed across the row to a hidden column and if the value is 0 I have a basic macro that "hides" the zero value rows. I understand I could further automate the process by having an "event" based in the spreadsheet which when the calculated values change the macro will run. This would mean when looking at the next department the zeros would automatically be hidden. I suspect I need a worksheet_calculate event but not been able to make it work.
View 2 Replies View RelatedThe file consists of 3 sheets:Orders,Order Form PO1 & Purchase Order. When I mark "x" in the row that I want to print, Order Form PO1 will be displayed with all data in the specific columns. However, it only allows me to select one row at a time. Now, I want to select multiple rows so that it will pass the data to the sheet Purchase Order.
View 13 Replies View RelatedI have set up the attached workbook to try to show the results that I need using the matching shaded cells in the worksheets.
The first part of the problem is that all data in columns A, B, C & H (Input1) needs to be copied to columns A, B, C & F in Input2 with the addition of a Zero value in columns D & E (sample data lines 1 - 3).
The second part of the problem is that all data in columns A, B, & F (Input2) where the cell in column C is equal to ZERO needs to be copied to columns A, B & C in Input 3 (sample data Lines 4 - 11).
It would also be useful to be able to copy data in columns A, B, & F (Input2) where the cell in column C is NOT equal to ZERO to columns A, B & C in Input 3 (sample data Lines 12 - 25). However, there will be often be more than value in column C (Input2) dictated by the date in Column A and number in Column B. The value in Column F (Input2) will always be the same for each of those rows, so it does not need to be duplicated in Column C (Input3).
This would have to be manually triggered by linking it to a button or menu item, not sure what would be the best option. As data is entered at various times of the week, the routine would either have to overwrite existing matching data or carry on from where it last finished!
enables the user to select a row to delete. However, I need to do more than that. I need to be able to copy all the rows until row 37 and move it one row up after the selected row is deleted. Below is the code
View 3 Replies View RelatedFor the sample data below, columns A – C contain survey response
data: 3, 6 and 9. I'm looking for advice on writing a function for column D
that would look at columns A – C and return the # of the column containing
the min value (e.g. A in this example).
A B C D
3 6 9 A
I've got a sheet that I build from a weekly sheet. I can have 30 to 60 rows, but I always have 10 to 20 rows left over with data in column C, none in A, my last code is "Range("A1").End(xlDown).Offset(1, 0).Select" which takes me to the last cell in column A. Any code that I can then delete say 20 rows after that? I tried one code, but it wanted to delete everything below the cell selected which ran & ran.
"-" are blanks. Range moves the selected cell to A4. I want to delete row selected + 19 more.
-|A|B|C|D
1|Y|2|9|J|
2|R|1|8|N|
3|G|4|7|N|
4|-|-|L|-|
5|-|-|L|-|
I have some selected cells in column A, they are not consecutive. I would like to copy these values and then copy the same area but from a different column...
The code I have here is only working when I have a consecutive selection.
Code:
Set rng = Selection
fr = rng.Cells(1, 1).Row
lr = rng(rng.Count).Row
rng.Copy