logo
down
shadow

vba Loop over a non-contiguous range


vba Loop over a non-contiguous range

By : jmkse
Date : November 20 2020, 07:00 PM
I hope this helps . I have a non-contiguous range on rows (example address of myRange: $2:$2,$4:$205,$214:$214) and I would like to access a specific row and column within the range. I have tried the following: , Here are my entries - not necessarily better than Irwin's
code :


Share : facebook icon twitter icon
For loop with non-contiguous range

For loop with non-contiguous range


By : Omid
Date : March 29 2020, 07:55 AM
should help you out I have a loop that runs through each column and sets the value to an R1C1 sum of a few rows above it. I'm using a for loop, but I want to skip a few columns, as they contain formulae already in the cell. How can I set up a loop that only cycles through a non-contiguous set or numbers? , A VBA version of Learning's C# answer:-
code :
Dim col As Integer: For col = 1 To 70

    Select Case col

    Case 25, 36, 37, 44, 60, 63, 64, 67, 68, 73, 75, 76
        'do nothing'

    Case Else
        'do something'

    End Select

Next col
How to change part of selected range in a selection of multiple non-contiguous range

How to change part of selected range in a selection of multiple non-contiguous range


By : Fluo
Date : March 29 2020, 07:55 AM
I wish this helpful for you If you assign a Name to the range, the cells will be adjusted after the column is inserted:
code :
Sub RememberTheCells()
    Range("A1,C3,D4:E6").Select
    Selection.Name = "Previous"
    Columns("B:B").Insert Shift:=xlToRight
    Range("Previous").Select
    MsgBox Selection.Address
End Sub
Trying to loop non-contiguous rows in a range

Trying to loop non-contiguous rows in a range


By : Yuriy Smolin
Date : November 16 2020, 03:01 PM
fixed the issue. Will look into that further I tried to simplfy your code in the comments for testing purpose, but the following code should help,
code :
Dim x As Integer
Dim myrange As Range
For x = 1 To 30
   If (mainTasks.Cells(x, 2) = "completed" Or mainTasks.Cells(x, 2) = "Dismissed") And mainTasks.Cells(x, 3) <> "" Then
      If myrange Is Nothing Then
         Set myrange = mainTasks.Cells(x, 2)
      Else
         Set myrange = Union(myrange, mainTasks.Cells(x, 2))
      End If
    End If
Next x
myrange.ClearContents
Replace contiguous duplicates by a set value, but only if length of contiguous range more than a threshold

Replace contiguous duplicates by a set value, but only if length of contiguous range more than a threshold


By : user2911794
Date : March 29 2020, 07:55 AM
will be helpful for those in need As said in the comments, using GroupAdjacent to group contiguous duplicates using the nuget package MoreLinq is an option:
code :
var strings = new List<string> { "red", "red", "blue", "green", "green", "red", "red", "red", "red", "red", "red", "yellow", "white", "white", "red", "white", "white" };

var result = strings
    .GroupAdjacent(x => x)
    .SelectMany(grp => (grp.Count() >= 5) ?
                grp.Select(x => "ignore") : 
                grp);

Console.WriteLine("{ " + string.Join(", ", result) + " }");
{ red, red, blue, green, green, ignore, ignore, ignore, ignore, ignore, ignore, yellow, white, white, red, white, white }
How do I find the largest contiguous n length range within a larger range

How do I find the largest contiguous n length range within a larger range


By : aantunovic
Date : March 29 2020, 07:55 AM
I hope this helps . I am trying to find the cell references for the largest n length contiguous subset of values in my range. , Use this:
code :
Function MaxN(n&, r As Range)
    Dim i&, j&, m#, t#, v, str
    v = r.Value2


    For i = 1 To UBound(v)
        If UBound(v) - i + 1 >= n Then
            t = 0
            For j = i To i + n - 1
                t = t + v(j, 1)
            Next
            If t > m Then
            m = t
            str = i
            End If
        Else
            Exit For
        End If
    Next
 MaxN = Range(Cells(str, r.Column), Cells(str + n, r.Column)).Address
End Function
Related Posts Related Posts :
  • VBA - Using criteria list select and clear or select an replace values
  • SSAS 2008/Excel 2007 - Can't see cube
  • Output a NULL cell value in Excel
  • Excel VBA Clear formats when value is 0
  • Value as hyperlink?
  • Adding references from VBA Causing Password Prompt To Appear
  • keep getting "Next without for" while I start with for
  • How to color 56 cells in B column
  • Excel from web scraping
  • Nested IF statements in Excel [Over the 7 allowed limit]
  • How can I copy data from one sheet to another sheet in Excel 2007 using a macro?
  • Return a value of '1' a referenced cell is empty
  • How to create and update spreadsheet using OLEDB in C#?
  • Excel turning my numbers to floats
  • how to set new range variable to one cell address
  • Include new data entered each day in concatenate formula
  • SSRS export to Excel UNFORMATTED
  • Is it possibule to insert 200 JPG image together in the excel file with the same size into its cell?
  • Lotus Notes Database Search
  • Extracting text from number of webpages list in Excel
  • how to remove special characters from all the cells in sheet
  • How can I clear the contents of (only) hidden cells in Excel (and not delete them)?
  • Using Application.OnKey to Disable CTRL-V
  • Not able to find unique values in excel
  • Excel stealing keyboard focus from VCL Form (in AddIn)
  • Multiplying an amount by 0.45 up until 833 then the remain by 0.25
  • Sort multiple rows Left-to-Right
  • Sorting data in columns with variable number of columns
  • Speeding up macro that goes through a workbook overwriting sheets by value
  • How can I optimize this macro that sorts and formats SQL data?
  • Copy a cell to a merged cell while keeping hyperlink value
  • Multiple IF arguments layered between cells
  • 432 4.3.2 STOREDRV.ClientSubmit; sender thread limit exceeded?
  • How to convert unusual datetime formats in excel to year, month, day, and time cells respectively?
  • Flashing animation getting interrupted by DoEvents
  • Delete multiple Excel Sheets in VBA
  • Delete text (left or right) with VBA so I only keep a specific string
  • I Bad: VBA Confusion
  • Add spaces to a string in excel vba, later used in .HTMLBody
  • Using While loop for getting the handle of rows in a particular Column
  • How to perform SUM of multiple VLOOKUP values by treating N/A values as Zero?
  • FIND function in VBA - Object does not support this property or Method
  • VBA Excel: “Automation Error. Exception occurred.” when using UserForm
  • Find specific lower or uppercase
  • group the results of a matching function
  • "Standard Deviation If" in VBA
  • How to use Conditional Formatting?
  • Match Function Shows Incorrect/Repeat Position
  • Add character to the beginning of a cell that start with number
  • How can I handle DLL errors in VBA?
  • Copy a Hidden Excel Sheet
  • How to import a table from web page (with "div class") to excel?
  • format excel file after export-csv powershell
  • Cancel = True is not working in Workbook_BeforeClose
  • Is there a way to retrieve information from a text file in vba?
  • Comparing two columns and if it matches then it should be highlighted in another column
  • How to get temperature of datetime value based on the table which contains day and night wise temperature data
  • How to Define a Pivot Cache?
  • Named argument not found vba compile error
  • Calling multiple Sub's with one line
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk