range address

  1. L

    Create a dynamic start and end in range to paste into

    I'm fairly new to VBA. I have a piece of code which copied all rows that have numeric data in Column K from one spreadsheet to another. This will be run on multiple spreadsheets, which have difference exercise names and job numbers. The exercise as job number only appear in one cell on the...
  2. S

    Copy and paste the Range address displayed in the Msg Box below subroutine to any selected cell

    I have a following code which displays the Range address in the msg box after executing the code I am looking to copy and paste Range address to any of the Selected cell. Sub GetRangeAddress() Dim r As Range Dim s As String Select Case Selection.Areas.Count Case 0: MsgBox "Nothing...
  3. lynxbci

    Object required

    Hi, I am trying to select a column range by first finding which column has the title I require (in this case "UseThis") Then I want to select the range from that address (f.address) and offset it by a number of rows (in this case I am using 100) The code works and msgBox shows $H$1 as expected...
  4. Worf

    Worksheet page addresses with VBA

    Sometimes we need to know the addresses of all the pages that make up a worksheet. The code below lists them and offers an option to colour each page differently. Sub Main2() PageAddress2 True End Sub Sub PageAddress2(colorcode As Boolean) Dim c%, v%, h%, cln%, rw%, hgth%, wth%, i%, ws As...
  5. F

    Dictionary - defining range

    Hi I would like to add a list of items to a dictionary and then loop each item. The problem is, I don't know how to refer to a list range that is on another worksheet than the main data that is being filtered. "Items" are located on sheet wsList, so where am I suppose to add it? Dim dict As...
  6. R

    Run-time error '1004' Method 'Range' of object '_Global' failed

    Can't quite see/figure out what I've done wrong (I don't know what I don't know :confused: ). I'm getting: Run-time error '1004' Method 'Range' of object '_Global' failed I've tracked the error down (I think) to the FOR-NEXT loops in the following snippet of code (edited for...
  7. M

    How to copy selection.address cell values to another workbook.?

    hi, I am new to VBA and tryin to reduce some workload to make a small VBA program to copy range randomly with mouse and paste it to destination file. source file is "Mytest.xlsm" and destination file is "BatchEntry.xlsx" I can easily copy range by below code and successfully paste It to other...
  8. R

    Finding last used cell in a Range and ADD form values into the Worksheet

    Hello guys, Need some help here. I have a form and I need my code to update the form values into a specific range (M55:Q65) in my worksheet. <tbody> Rno M N O P 52 End of Booking Data 53 54 55 eDM Name Tot sends Uniq OR Uniq CTR 56 Need the data to updated 4rm this line...
  9. D

    Persisting Range Name Addresses to Avoid #REF!

    There must be a technique of which i am ignorant to preserve the addresses or the formulas a range name refers to. A workbook has been returned from coworkers with unexplained #REF # errors in some range name addresses. So, how is this best fixed, that is, where and how should this...
  10. C

    using variables in formulas

    I am trying to make the row number dependent on the "temp" variable. But I keep getting an error. How should I write the code so the it will run? Thanks. Dim Temp As Integer Temp = ActiveSheet.Range("A1").End(xlDown).Row - 1 Range("I" & Temp + 1).Formula = "= sum(I3:I" & temp")"
  11. O

    Define a Range for every N rows and do something if the cell is selected

    Hi Community, I am using Excel 2010. I am trying to Hide/Unhide rows when specific cells are selected in a Worksheet. I have managed to do so with the following code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim UnHideRange As Range Dim HideRange As Range Set UnHideRange...
  12. N

    VBA to replace row numbers in a range with variables e.g Range("$A$RowFirst:$B$RowLast")

    Hi I have 35 rows and 2 columns A and B I want to filter by TopTen values in column B in rows 8 to 25 I have used the following to find the first and last row number (thanks to WBD) Dim rowNumberFirst As Double On Error Resume Next Err.Clear rowNumberFirst =...
  13. M

    How to get cell ranges based on value in the cells

    I'm trying to cut down the number of variables I have for solver. I have a large group of cells that could all be changed, but have identified the ones that will actually need changed with a 1 as the value. How do I get the cell range(s) of just the cells with 1 in them? Here is an example of...
  14. J

    VBA for formatting cells to be same as other cells

    Hi looking for some help with formatting cells. I currently have a spreadsheet that automatically populates depending on a value that is in a certain cell (target address). The information (target range) is pulled from another sheet ( Sheet 25) and then dropped into the named area I need...
  15. K

    vba need help referencing other cells

    Hello all.. learning this vba stuff one google search at a time is tough! I'm knee deep into a project that's well beyond my skill set.. but learning fast. The trouble I'm having is how to reference other cells in proper syntax.. and understanding how to specify a range... can someone get me...
  16. A

    using range address from input box

    in below vba code , i would like to replace all "B" in this code by range selecting or entering from application input box , kindly advise , Sub ahmed_code() Dim lr As Long, r As Long, x As Long Application.ScreenUpdating = False lr = Range("B" & Rows.Count).End(xlUp).Row For r =...
  17. B

    Problem with top property in a range variable

    I'm using shapes.addline method in a sub to draw a line, passing in 2 ranges rFrom and rThru, and then using .top and .left on the ranges to supply the four points for the line. Seems pretty straightforward, but the line sometimes comes out at an angle when I've passed in ranges on the same...
  18. V

    Selecting a range with blank cells

    Hi, I have a range with blank cells like this <tbody> Store name Item 1 Item 2 Item 3 Store A 2 3 4 Store B 1 Store C 1 1 Store D 1 1 </tbody> If blank cell is in between the range, code is not copying entire range. Sub conc() Dim myRow As Long Dim storename...
  19. L

    Resize range does not resize address

    I have just found something that seems strange to me. If I resize a range the address of that range does not get resized at the same time. This then prevents being able to step through the range using a For Each loop. Question: do I really have to SET the range so that I can loop through it...
  20. C

    cba copy and sort calling a sub

    Hi all, VBA newbie here. I am trying to copy a range (three columns, varying number of rows) in a second worksheet without blanks and sorting the data according to one of the columns (integers). I am trying to do this with 1 sub to define the ranges and a second to actually do the sorting...

Watch MrExcel Video

This Week's Hot Topics

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 MrExcel.com.
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 "mrexcel.com".
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