Using CurrentRegion
Jun 10, 2006
I've been using a piece of code for a while now, but don't really understand how it works. I was hoping someone could help me disect it so I could fully understand how it works.
Sub HLight_UAJ()
Set rFormula = Sheet3. Range("S9:S" & Sheet3.Cells(Rows.Count, 2).End(xlUp).Row)
rFormula = "=IF(RIGHT(B9,4)=""0000"",TRUE,FALSE)"
Set rFilter = Sheet3.Range("A8:S8")
With rFilter
. AutoFilter
.AutoFilter Field:=19, Criteria1:="True"
.Offset(1, 0).Resize(. CurrentRegion.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible). _
EntireRow.Interior.ColorIndex = 7
.AutoFilter
End With
End Sub
I understand everything except this part
.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible). _
EntireRow.Interior.ColorIndex = 7
I know it highlights the entire row of the visible part of the autofilter, but I don't understand how it chooses what to highlight. I need to know how this works so I can adjust it. I don't want to highlight the ENTIRE ROW, just from Column A to R.
View 9 Replies
Apr 24, 2006
'macro sortSales
'You will want to use the CurrentRegion property and look at
'all references to ranges. Any method that does not use an
'If statement, or anything similar, and doesn't change the
'worksheets, except by sorting, is acceptable. Be sure it would
'work on worksheets similar to these but not identical
'As you make changes to the macro, test that it still works for
'Test-Sort 1before you test it for Test-Sort 2
Sub Macro5()
Range("A1:G39").Sort Key1:=Range("D2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End Sub
This wasn't in the lecture and I have no idea where to begin correcting it... The worst part is that I have finals all next week and I need to study for them. I usually was able to find my answers on this forum but I don't seem to be able to do that in this case... any help GREATLY appreciated!!!!
I've included the above macro in the attached file.
View 9 Replies
View Related
Nov 22, 2006
Assuming I have a data martix which is X* Y. I want to store a range that is at Row Z. I have named a single cell at Row Z as "dataStart". How can I store that Row without the header?
View 5 Replies
View Related
Oct 28, 2006
Probably a really simple solution to this one, I'm trying to create a macro that will jump to a location, select the current region and then create a range name for that region. However, the number of rows in the region will change each time the macro is run as the region is a result of an advanced filter from a huge database. This is what I have managed so far but it to no avail
Selection.CurrentRegion.Select
ActiveWorkbook.ActiveSheet.Names.Add Name:="mergea", RefersTo:=Selection.Address
Ultimately the data selected will become a list for a Word mail merge
View 5 Replies
View Related
Mar 1, 2013
I have a spreadsheet in which the amount of data is not predictable. There are always a different number of rows and columns in this spreadsheet. I need to be able to grab the data that is in it starting with B1 and going to the last row and column with data and put it into a scatter plot. The code that is currently in my macro is as follows:
ActiveSheet.Range("B1").CurrentRegion.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range("ForChart!$B$1:$J$1000")
Note: ForChart is the name of the sheet where the data is housed; I have other sheets in this macro.
I know that the last line is the problem. While I figured out how to select only the data I need, when it goes to put it into the chart, it's still using an absolute reference and I don't want that. There are sometimes more columns than J and fewer rows than 1000. I want that data range to be whatever CurrentRegion selected. Is that even something that Excel can do?
View 3 Replies
View Related
Sep 16, 2008
I'm trying identify the active cell row number in a current region for viRows below. Currently I'm getting the active cell row number on the total worksheet.
With ActiveCell.CurrentRegion
viCols = .Columns.Count
viRows = .Range(ActiveCell.Row).Count
End With
View 2 Replies
View Related
