variable range

  1. C

    Find a substring within a range and return the full string.

    Given three variables (starting row, ending row, and substring) return the full string of the first cell found in the given range that contains the substring. What formula can I use in D7 to return the string found within the given rows? Looking to be able to change the rows as needed. For...
  2. S

    VBA MACROS - keyboard shortcut range code

    Hello, I’m not sure this is the best way to go about it.. But I need code to get a specified range in a macro. The range is variable .. so I’m trying to use keyboard shortcuts like “ctrl + home” and “ctrl + end” to get the correct range. The problem is when I start let’s say at the bottom...
  3. J

    Help modifying an existing macro to remove all of the "fixed" ranges in it

    I have taken over a project that someone previously developed the macros for. I would prefer it to work regardless of the dataset being used, but the person that developed it used mostly "fixed" ranges and I would prefer that it determines the range based on the data set. The beginning of the...
  4. T

    Worksheets.range variable vba

    Hi, I am trying to copy certain row values to another worksheet based on which row the user clicked in Column A. For example if user clicks Cell A3, i want to be able to copy the contents of cells D3, E3, G3 to another worksheet. This code works if i know the user will click A136 but i need...
  5. T

    VBA automatically update part of range variables based on last column

    My code contains a number of range variables such as the following, which I need to update automatically based on the number of columns (where T is the last column with data, and U repeats the references in colA). Dim fmtRng As Range: Set fmtRng = Range("B3:T15, B20:T32, B37:T49, B54:T66...
  6. D

    Filter 3 columns of data using 1 criteria, and copy to a different workbook

    Hello, I need to do the following: Being in Workbook1, activate Workbook2 which contains the data below; filter the data in Columns A to C so that only the "c"'s in column a remain ; copy only the visible data and paste it into Workbook 1...
  7. G

    Setting a SetRange to the highlighted selection

    Hi Team, Forgive me for some of my lack of knowledge of syntax and proper definitions on even the beginner details of VBA, but I have a certain scenario I need help with. Basically I have four seperate ranges on one sheet (Stacked on top of each other with one row between each). These ranges...
  8. C

    Using Sumifs in VBA where range changes

    Hi I am using a simply Sumifs formula, however this is across a data range that will change This is the formula and works as is Range("L6").Select ActiveCell.Formula = "=SUMIFS('Apps Indicative Earnings Report'!$K$11:$K$73,'Apps Indicative Earnings Report'!$G$11:$G$73,'INVOICE CALC'!H6,'Apps...
  9. M

    using variables with range(cells) reference

    Hi, I'm trying to build a range based off of variables that are calculated by user input. I have been able to get everything to work except for the range itself. I keep getting the "method of range of object _worksheet failed" error. The input is on one worksheet and the range is on a...
  10. MacroNoob1

    How can I make a macro calculate formulas for a variable number of rows?

    Hi everybody, This is my first post, so a big thanks to anyone who will help! Here's the thing... I routinely export a dataset into Excel that has a different number of rows every time. I would like to make a macro that calculates several formulas at the bottom for each column. For example...
  11. D

    Dynamic range of cells

    Hi, I'm trying to have a range of cells vary according to other factors. INDIRECT(ADDRESS(1;MATCH("Y";AT2:BZ2;0) What is highlighted in red is what I want to change according to other formulas. The thing is you can't put it in text so I don't know how to have it written in the formula to be...
  12. D

    Regrouping values of multiple cells according to variable data

    Hi, I am trying to automate a very long process with excel. I work in a motorsport business and I would like to do application charts automatically in excel. Let's say, a bearing fits on a XXXX 4-wheeler from 1996 to 1998, 2000 and 2002 to 2005. I want to have this written this way : 92-98, 00...
  13. V

    Autofilling multiple columns using a variable

    I'm completely befuddled on what seems like it should be an incredibly easy macro. (Only been working with excel a few days, so please don't hate me because I don't know what Im doing yet.) I'm getting different worksheets in that I plug in formulas across a few rows and then pull them down to...
  14. H

    SumIf / do until loop

    Hello, Instead of using a pivot to calculate totals for my subcategories, I would like to use the sumIf function in Excel VBA. After I dumped my transacation (variable length each month) in Sheet1 (in dutch: "Blad1"), I'm aiming to calculate the totals on a separate sheet ("Som transacties")...
  15. K

    Using Select Case to pass a variable to AutoFilter as Range

    Hello All, First off, thanks for the innumerable solutions, advice, tips and codes that I have used in the past, this is my first post but I've used this forum to solve many an intractable problem. All of my excel / VBA is self-taught, although I have a grounding in programming. Problem...
  16. B

    Excel VBA - Macro to delete nearly duplicate rows based on criteria

    I have a very large excel table that appears as follows: <tbody> Bob OH 3 Bob OH 4 Bob OH 5 Mary OH 2 Mary OH 3 Mary FLA 2 Ted AL 2 Ted AL 3 Ted NY 2 Jim NY 2 </tbody> I want to delete rows leaving only the row with the max value in column 3 The final table should appear as...
  17. B

    VBA Vlookup variable range

    still learning VBA and frequently bumping up to some errors. I currently have the following issue: I have an excel with 2 worksheets (FORM and DATA). sheet DATA contains 2 colums of data: A = lookup value. B = value to be returned. Range (A:B) is named data. sheet FORM column B contains the...
  18. E

    Macro writes SUMIF formula to a cell, Range and Sum_range need to be variable

    Hi all, The following line of code writes as SUMIF formula into cell E1: Range("e1").Value = "=SUMIF(b1:b10,""Materials"",Y1:Y10)" Range for the SUMIF formula always starts on b1 but the amount of rows needs to be a variable Sum_range also starts on Y1 but the number of rows needs to be...
  19. P

    Emailing a Variable Range as HTML

    Hi all so first of all the below code is an adaption of one from Ron de Bruin and not my own code. I have a spreadsheet that has accounts number and information in columns A:D I have Macro Buttons in row one and my column headers in row 2. What I want to do is email A:C from row 2 to the last...
  20. J

    Error 1004 using variable range

    The following five lines of code are inside a loop in which i goes from 1 to 600. When i was 594 a condition allowed these lines to be executed. (The last was in my original code and the others were just added to try to figure out why the last had a problem.) The first three work and the last...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back