1. J

    VBA - Select Range for Pivot Table (Not including Last Row)

    Hi Excel Experts, I would like to have your advise on how to select range for a pivot table that not including the last row because it is the "Total". I tried below methods but not worked. 1) This method will including the row 6 (Final row-Total) Selection.End(xlDown).Select...
  2. L

    Conditional Merge

    Good evening everyone, I had a question ... btw, excuse my bad english: I have a couple of tables, CONTRIBUTION and BILLING, which have an "ID" field with which I relate them (the relationship between the tables is * (CONTRIBUTION) -> 1 (BILLING)), as well as a CONSUMPTION (numeric) field , a...
  3. G

    VBA to search multiple cells.

    Hey guys, I have this simple code to export as PDF, range AB52 is where it pulls the file name to save as... is it possible to have multiple ranges for the code to look for? I tried "range ("AB52:AB54")" would not work. Is this even possible? If one cell is empty look on the other ones for the...
  4. W

    Convert Table to Range in all sheets but only for a given range

    Sub TurnTablesIntoRanges() Dim wks As Worksheet, objList As ListObject For Each wks In ActiveWorkbook.Worksheets For Each objList In wks.ListObjects objList.Unlist Next objList Next wks End Sub Hello, A novice here... I have few tables in each workbook in a multiply sheets excel file. Can you...
  5. W

    Sum the entire column of named range except the first row

    Hi Folks, I have a question, is there a way (a formula) that i can put in a first cell of a named column and have the formula sum the entire column EXCEPT the first cell of that same named range? This formula "=sum(index("My Named Range",,11))" results in a circular error because its trying to...
  6. C

    highlight cells more or less than 0.2 to nearest 1

    Hi, is there any way to highlight the cells in a range, if: the value is more or less than 0.2 to the nearest 1 there are already a formula in each of those cells in the range, which will change according to what i change at the other cell. example: if cell value = 6.09, highlight if cell value...
  7. K

    range error

    I'm new to VBA so be gentle. This just started happening today. Whenever I select a preexisting command button to a macro, I get an error window listing the range as "Wrong number of arguments or invalid property assignment" What can I do now? Sub clearswitchboard()...
  8. J

    SUMIF formula for Criteria that is a range

    Is there a good sumif formula or a different formula that would be good to use for a range of criteria and the criteria is all different but specific to what totals I what pulled out of a larger listing? Thanks!
  9. H

    Value corresponding to flag cell

    My input data is in Range1 Range1: B2:G12; values are either of +ve or -ve values or zero or null, columns are contiguous. There is another ‘flag’ range which is below Range 1 i.e. below B2:G12 at B14:G14. B14:G14 is either of 100 or <>100 with a caveat that at a time, at the most, only one 1...
  10. A

    multiple worksheet changes

    Hi I have a worksheet change in a sheet but need to a second one. I know you can't have two changes in a sheet so is there a way I could combine the two changes below? Private Sub worksheet_change(ByVal target As Range) If target.Column <> 2 Ortarget.Cells.Count > 1 Then Exit Sub...
  11. P

    Inventory VBA

    Hello, I've received and modified a simple VBA from another excel and i was wondering if anyone could help me modify even more. The thing i need is that the range of this code would be across all documents worksheets. I1,I2,I3 stays in the first sheet. Date and Name in any other if I2 or I3...
  12. N

    Range of Cell to exported as html website

    Hello, I am trying to export a specific range of cells (A1:E18 for one and I1:M18 for another) on a specific sheet and save it in a HTML table, separately. The code I have below is what I found and have been working with to try to modify but as I make changes I don't seem to be affecting...
  13. L

    fill a range with numbers - vba

    Hi The code below is going to fill a range with random number but all the cells will get the same number. What I want, is to fill cells of that range with different random number. So I am thinking to put that line of code in a loop but how can I loop when user might enter any different range...
  14. D

    How to output a dynamic range where the height of the range varies

    Hi, I have a search tool that returns a dynamic range. I'm trying to get it so that the outputted dynamic range is staggered. I get this: <colgroup><col style="mso-width-source:userset;mso-width-alt:5266;width:108pt" width="144"> <col...
  15. L

    Need assistance with VBA (array creation) for logarithmic regression forecast UDF

    MY GOAL: My goal is to create a uniform set of UDFs that perform forecasts under various forms of regression, such as exponential, logarithmic, and power. Excel has several native functions for forecasting under various forms of regression, but there are several and the syntax is not uniform. I...
  16. B

    using a cell reference in a range command in VBA

    Hi everyone, I am new on here and have a small problem. I want a command button to copy a range of cells and past somewhere else on the sheet. Simple enough I know, but if I have range("A1:A10") no problem, but I want range(A1:A&H1). I know this won't work, but I want the value to change to...
  17. H

    Replace all numbers with zero, except formulas

    I have a specified range selected. Range(ActiveCell.Offset(0, -9), ActiveCell.Offset(0, -3)).EntireColumn.Select Now, I have to replace all numbers with zero in the above range, except formulas. Can anyone help me?
  18. K

    Count data between dates

    Hello Folks , I am working on a file where in i am setting control limits "Upper Control Limit" and "Lower Control Limit" Sheet is working fine for me however need a small logic to be build in the below formula . =COUNTA(C24:C$523) In the above Formula "C24:C523 is the range where Dates are...
  19. S

    Help to modify code Worksheet_Change(ByVal Target As Range) to ByVal Target As Object

    Hello, I have this code that filters data by the selection in cell 8 (data variation). Can I please have your help to modify ByVal Target As Range to ByVal Target As Object so it filters by the selection made in combobox1? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address...
  20. J

    Hide rows in range when value in COL A = 0

    Hi All, Need some code to do the following. For a range of rows from Row 10 to 50000 Hide any row where COL A has a value = 0 Many thanks....sorry for such a numpty question. James

Some videos you may like

This Week's Hot Topics