Current Region Usage

Rayne
Rayne used Ask the Experts™
on
Hello Again Dave,

This question is in reference to
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27727416.html

CurrentRegion Usage Dangers:
Since you are not a supporter of current region – when you have a moment – you can look through the worksheet and let me know if there is any risk or problems with using current region here. Data is getting copied from A to B. B worksheet had some data sets after one blank column from the copied destination. This copying from A to B – will that create any issues – I can’t afford to lose the data set sets –dataI,dataII,dataIII,dataIV
So far the current region is working as expected with no deletion of the important data sets that surround the destination where the copying is happening.

let me know if there is any issues with this current set up or possible issues in future.

thank you
copyOnCondition101.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
It was UsedRange I mentioned can be faulty (re: the last row or column can be incorrect, and also includes any formatting - so if you have a font change at row 50000, then that entire area would be included in the UsedRange).

I am not aware of any problems with CurrentRegion, and formatting issues on the sheet do not impact how CurrentRegion works, though formulas that result in "" are evaluated as if they have data in developing the CurrentRegion address.

Dave

Commented:
You need to modify your programme

for example if add another dataset old data set going to delete, b'cos of that when you are entering new data record ,need to start from next cell and if someone add button 2 times without knowing if its alreday added no need to add again.
Most Valuable Expert 2012
Top Expert 2012
Commented:
By definition, The current region is a range bounded by any combination of blank rows and blank columns

So as long as the data is contiguous, the current region approach will work.

I rarely use currentRegion, but that doesn't mean you cannot.

Here's an alternative:
Sub Button1_Click()
Dim rw As Range
Dim wsA As Worksheet
Dim wsB As Worksheet
Dim rSource As Range
Dim lastRow As Long

    Set wsA = ThisWorkbook.Worksheets("A")
    Set wsB = ThisWorkbook.Worksheets("B")
    'Last row of range determined by the last cell in column B with a value in it
    Set rSource = wsA.Range("B6:S" & wsA.Cells(wsA.Rows.Count, "B").End(xlUp).Row)
    
    lastRow = wsB.Range("B:S").Find(what:="*", LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    
    If lastRow > 10 Then
        wsB.Range("B11:S" & lastRow).ClearContents
    End If
    
    For Each rw In rSource.Rows
        If rw.Cells(1, 9) <> "ot" Then
            wsB.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(rw.Rows.Count, rw.Columns.Count).Value = rw.Value
        End If
    Next rw
End Sub

Open in new window


See attached,

Dave
copyOnCondition101.xlsm

Author

Commented:
Perfect Dave, Thank you
It’s always good to know alternatives that way we are restricted to just one way of doing things.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial