rng

Hi, I wish to write a macro that will hide (and another one that will unhide) all the comments in an Excel Sheet in a selected range (which the user selects with the mouse). I started with a simpler example with a hard-coded range (1 cell only), and it works correctly: Sub...

Does anyone know how to keep the undo along with worksheet change event. Here is my code that I am using. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range 'TRACKS DATE ENTRY WAS MARKED AS COMPLETE AND WHO UPDATED IT If Target.Column = 21 Then...

Vba does recognize when the cell value changes in B7 to R7 because I pull cell value from another worksheet. it only works if I change the cell directly. any suggestions. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub Dim rng As Range Set rng =...

Hi Experts - I looked around and was able to build below code.. My goal is to convert the format of the numbers found in column R (that are in general/text format) into number. The line item is changing so i don't have the "fixed" number of the last cell with data. I also need the code to...

I am trying to use following VBA code to copy and pastevalues only.I want to modify it where code can automatically find last cellused (this case I mentioned A1:D29 I would want system to find on its own. Please advise. Sub TransferValuesOnly() Dim rng As Range Set rng =...

I have the following: Sub Automatic_Highlights(Rng As Range) If Rng.Range("O:T") = True Then GoTo Skip ' In my head this should read "If Rng falls within Range("O:T"), Then GoTo Skip"; Or that's how I want it to read at least. Rng.Interior.ColorIndex = 7 Intersect(Rng.EntireRow...

I have a button that adds 'x' amount of rows when desired number is entered. I feel this VBA is close and need help adjusting it. My data starts on row 5 and would like new blank (but containing formulas) added above the existing table. E,g,. pushes rows down. A run-error '1004' pops up saying...

VBA rookie back for help. Not sure if what I'm trying to do is possible (or at least as simple as I'm trying to make it). I have a workbook where I have a macro that splits a main sheet into new sheets based on unique email addresses. I now want to email each sheet based on the email address...

Hi there, I use the below code to automatically reveal a row as the user enters details. It's been working fine but in this case, there are many ranges to take into account and when I tried to put it all under one, it came back with a syntax error (it's too long). Therefore, I am trying to...

Wrote a long explanation and it didn't post, so simply put: can a range be named using a variable? e.g. Set Rng = "Range" & N Then Rng becomes Range1, Range2, etc in a loop. This works to name sheets as variables ( sh = "Sheet" & M). Doesn't seem to work with a range name. Get "type...

Hi. I have this string that represents rows and row ranges separated by comma. str = "1,3,6:8,11:12" I want to join those rows in a single range variable (rng). I'm trying with this loop but the issue is initially rng is nothing and the "Union("nothing", Rows(s(0)))" gives error...

I'm trying to set a range variable but I keep getting an error (Run-time error ' 1004': Method 'Range' of object'_worksheet' failed. Please help me to understand what I'm doing wrong. Dim rng As Range Set rng = Sheet24.Range(Cells(1, 1), Cells(41, 22)) Jeff

I am using this macro to insert one column and then to paste a formula in each row of the column. It works except that I want to do it for two columns and two different formulas. Sub Insert_Col_Form () ' ' Dim Rng As Range Columns("F:F").Insert Set Rng = Range("F2:F" &...

The code below runs fine. But I also want to use it to take the results (Dn.Offset(, 6).Value = R.Offset(, 5).Value) and multiplies R.Offset by the value in in the current row. Sub Find_Parent_Quantity() 'Find the Parent's Quantity and place it in Column K Dim Rng As Range, Dn As Range, n As...

I have code that someone on this site graciously provided a long time ago. Below is what I required as is the code MickG provided. I am trying to use this code again, but It doesn’t like the format of the data in column A. If I type over whats in there, the code runs fine. My problem is the...

I am stepping through and deleting marked rows in my sheet. If I miss the header row the macro runs but fails to delete one highlighted row. If I include my header, I get a run time error. I have fixed it my including i = 1 before the loop ...but I cannot get my head around my it fails...

Hi all, I have a worksheet which is logging finances (incoming/outgoing) on the relevant dates, not every date is available if not used. Currently I have a code which works for a specific date, it moves me to the cell with today's date. Is it possible to go to a 'nearest' date instead as this...

Hi all,The code below sorts the figures out in each block of rows then places the position of that figure in the adjacent column,like this where the smallest number is 1 second smallest 2 and so on. CI CJ 1 24 2 26 3 29 4 34 5 35 6 37 <code>Sub rankandSort()...

I asked something similar a while ago, but could never get a solid answer. Here goes again. I want to understand why and not an alternative solution. Any help would be appreciated. Suppose I have a 2x2 table with header and want to write to the first row like this: set rng =...

In My Range G20:G32 I Have: <tbody> Excel 2010 <colgroup><col style="width: 25pxpx"><col></colgroup><thead>#DAE7F5[/URL] "] G </thead><tbody> 20 Able 21 Bob 22 23 Cathy 24 25 Daryl 26 Edward 27 Fred 28 29 Marvin 30 31 32 Pete </tbody> Sheet1...