range address

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...

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...

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...

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...

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...

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...

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...

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...

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...

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")"

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...

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 =...

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...

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...

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...

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 =...

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...

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...

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...

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...