Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Activesheet usedrange rows count Results

Hi

I'm using ActiveSheet.UsedRange.Rows.Count to identify the last row in a Pivot Table but when pivot table is updated and number of rows is less than the previous version it still counts the rows as if it's the old Pivot Table defining the area. Funny part is it only does it in the first sheet. All the following sheets is correctly updated. Any way to get around this?

Thanks,
Kjaer

Some details:

Old Pivot Table is 59 rows
New Pivot Table is 46 rows

I'm deleting the data not part of the Pivot Table using this code (PT has been updated at this point of time):

Range("I13:L65536").Select
Selection.Delete
Selection.Interior.ColorIndex = xlNone

Then I'm trying to identify the number of rows with this:

Dim myCount As Integer
myCount = ActiveSheet.UsedRange.Rows.Count

But when I copy the formulas I need and try to paste them next to the PT using this code:

Range("I12:L12").Select
Selection.Copy
Range("I13:L" & myCount).Select
ActiveSheet.Paste

it still assumes 59 rows

Good morning, all,

The last entry in my worksheet is in row 15 yet
activesheet.usedrange.rows.count reports 16 - or whatever the lowest entry
is in the worksheet+1
Another worksheet in the same workbook using the same code retruns the
correct value.

I tried deleting all the rows below the last entry, to eliminate the
possibility of spaces etc, but to no avail - does anyone have any ideas as to
what might be going wrong?

Thanks in advance

Pete

I am trying to create a function to count number of items in a column excluding row1 which has headers and save the last row as a reference so I can insert this into a formula.

So I have a basic formula which will be repeated for several different columns.

This will go something like this.
Range("B23").Select
	ActiveCell.FormulaR1C1 = "=SUM("Z2":("RowRef", "Z"))/ClassCount"
	Range("B23").Select
And this is where I am going with trying to create a function to insert the class count and row refernce.
Dim lastrow As Long, i As Long
	Dim RowRef = int
	ClassCount = int
	lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lastrow To 2 Step -1
    ActiveSheet.UsedRange.Rows.Count("B22").Paste
	ClassCount = ActiveSheet.UsedRange.Rows.Count
	RowRef = Not Sure	
	End Sub


Hi all ,
I am not that good in VBA. but i can use your help to be a GURU.

I asked the same questions in mrexcel forum , but i didn't get any help.
http://www.mrexcel.com/forum/showthr...next-empty-row

I have a UserForm which contains 4 textBox and a button named as follow:

(the textBoxes : mytxt, mytxt2, mytxt3, mytxt4) and (the button : cmdAdd)

on the spreadsheet , i have the following
Range "name1" is ("A:A")
Range "name2" is ("B:B")
Range "name3" is ("C:C")
Range "name4" is ("D:D")

What i want to do is to use the UserForm to add students names to the columns A,B,C and D.so far so good.

The Problems are :
1- i want to add the data to the ranges in the spreadsheet. i wrote some codes to add the data to the first empty row. the first data (mytxt.Text) was added a row above the rest. but this happens in the first time i add data.After that the data was add at the same row.

2- i want to check if the whole name (mytxt.text And mytxt2 And mytxt3 And mytxt4) -which will be in a row- already exist or not. if not add it to the first empty row. if it exists, notify me.

here is my codes


	VB:
	
 cmdAdd_Click() 
     
     
    Dim FirstName As String 
    Dim FName As String 
    Dim GName As String 
    Dim FamName As String 
     
    Dim Rng As Range 
    Dim Rng2 As Range 
    Dim Rng3 As Range 
    Dim Rng4 As Range 
    Dim FirstEmpRow As Long 
     
    FirstName = mytxt.Text 
    FName = mytxt2.Text 
    GName = mytxt3.Text 
    FamName = mytxt4.Text 
     
     
    FirstEmpRow = ActiveSheet.UsedRange.Rows.Count + 1 
     
    If Trim(FirstName)  "" Then 
        With Sheets("Sheet1").Range("name1") 
            Set Rng = .Find(What:=FirstName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ 
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Rng Is Nothing Then 
                MsgBox "Exsits !!!" 
                Application.Goto Rng, True 
            Else 
                ActiveSheet.Range("name1").Cells(FirstEmpRow, 1).Value = FirstName 
                mytxt.Text = "" 
                 'MsgBox "Nothing found"
            End If 
        End With 
    End If 
     
     ' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     
    If Trim(FName)  "" Then 
        With Sheets("Sheet1").Range("name2") 
            Set Rng2 = .Find(What:=FName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ 
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Rng2 Is Nothing Then 
                MsgBox "Exsits !!!" 
                Application.Goto Rng2, True 
            Else 
                ActiveSheet.UsedRange.Cells(FirstEmpRow, 2).Value = FName 
                mytxt2.Text = "" 
                 'MsgBox "Nothing found"
            End If 
        End With 
    End If 
     
     '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     
    If Trim(GName)  "" Then 
        With Sheets("Sheet1").Range("name3") 
            Set Rng3 = .Find(What:=GName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ 
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Rng3 Is Nothing Then 
                MsgBox "Exsits !!!" 
                Application.Goto Rng3, True 
            Else 
                ActiveSheet.UsedRange.Cells(FirstEmpRow, 3).Value = GName 
                mytxt3.Text = "" 
                 'MsgBox "Nothing found"
            End If 
        End With 
    End If 
     
     '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     
     
    If Trim(FamName)  "" Then 
        With Sheets("Sheet1").Range("name4") 
            Set Rng3 = .Find(What:=FamName, After:=.Cells(.Cells.Count), LookIn:=xlValues, _ 
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) 
             
            If Not Rng4 Is Nothing Then 
                MsgBox "Exsits !!!" 
                Application.Goto Rng4, True 
            Else 
                ActiveSheet.UsedRange.Cells(FirstEmpRow, 4).Value = FamName 
                mytxt4.Text = "" 
                 'MsgBox "Nothing found"
            End If 
        End With 
    End If 
     
     'Debug.Print FirstEmpRow
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello Everyone,

I have an excel workook with alot of entries. The entries consist of items with several relative columns and rows associated with it.

Below is a snippet of the items and their relative data. What I want to do using VBA is search through all the values in columnA on sheet1 and for each value that is duplicate, write the single value onto sheet2.

Next I want to write the relevant RM and RM Qty next to the value on sheet2. From their I want to find the corresponding BOX associated with the item and write it and it's RM QTY after the other RM items.

Below this first example is the result I would like to see. I found a code that semi works, but it does this on the active sheet using activeCell. I want it to write on another sheet.
ITEM RM RM QTY A D8500WHI-WHI 900 A BOX0111 0.25 B D8500WHI-WHI 822 B BOX0111 0.25 C D8500WHI-WHI 546 C BOX0111 0.25
This is how I want it to look on another sheet.
ITEM FILL WEIGHT BOX CASEPACK A D8500WHI-WHI 900 BOX0111 0.25 B D8500WHI-WHI 822 BOX0111 0.25 C D8500WHI-WHI 546 BOX0111 0.25
Here is the code I found:

	VB:
	
 Macro1() 
    Application.ScreenUpdating = False 
     
    Dim ColumnsCount As Integer 
    ColumnsCount = ActiveSheet.UsedRange.Columns.Count 
    ActiveSheet.UsedRange.Activate 
    Do While ActiveCell.Row

Hi Everyone!

I have a macro that I am using and it works perfectly but I just want to tweak a little bit but I don't know how. This macro will take you back to another sheet from active sheet to the last record row but the cursor always ends up at column D. I would like to have the cursor location to end up at the last record row of column B instead.

Here is my macro code:


	VB:
	
 CPListUpdate() 
    Worksheets("CPList").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(1, 3).Value = _ 
    Array(Cells(24, 1).Value, Cells(24, 7).Value, Cells(13, 4).Value) 
     
    Range("D1").Select 
    Sheets("CPList").Select 
     
    x = ActiveSheet.UsedRange.Rows.Count 
    ActiveCell.SpecialCells(xlLastCell).Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Again, the cursor always ends up at column D of the last record. I would like to have the cursor at column B instead.

I would appreciate everyone's help on this and thank you in advance.

RNF

When I copy and paste all four columns manually by using "end home" to find the last row there is not a problem.

I had to modify code that copied formulas from row 3 of col F G H I, as a group, and pasting to last row, to copying and pasting one column at a time. Instances where there were more than 2200 rows would return runtime error "Selection Too Large" .
The following works but looks rather sloppy. Does anyone have any suggestions to make this portion more efficient? The number of rows can range from 20 to 8000. The spreadsheet is being used to generate random numbers in order to pull samples of work. The spreasheet is cleared and rows are deleted prior to each use.

original

	VB:
	
Sheets("FX566604").Select 
num_Rows = ActiveSheet.UsedRange.Rows.Count 
Range("A3").Select 
Selection.Copy 
Range("a4:a" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("f3:i3").Select 
Selection.Copy 
Range("f4:i" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("b1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
modified to

	VB:
	
Sheets("FX566604").Select 
num_Rows = ActiveSheet.UsedRange.Rows.Count 
Range("A3").Select 
Selection.Copy 
Range("a4:a" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("f3").Select 
Selection.Copy 
Range("f4:f" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("g3").Select 
Selection.Copy 
Range("g4:g" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("h3").Select 
Selection.Copy 
Range("h4:h" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("i3").Select 
Selection.Copy 
Range("i4:i" & num_Rows).Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 
Range("b1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As always, thank you for your help and, in this case, valuable input.

Hi Guys,

I have a problem with deleting rows that contains invalid formulas, these formulas are generated randomly daily in rows that contain no data and refer to empty cells in the same row.

Is there a code to delete rows an empty row in that by data it is empty, but not so because there is a formula?

currently this code of mine doesnt work on the zero data but formular-ed cell.. cause counta on the cell gives me 1


	VB:
	
 
EmptyR = ActiveSheet.UsedRange.Rows.Count 
For i = EmptyR To 1 Step -1 
    If Application.CountA(Cells(i, 1).EntireRow) = 0 Then 
        Cells(i, 1).EntireRow.Delete 
    End If 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thanks...
lloyd

Hey All,

I am a newbie here, but am not new to VBA. I am trying to complete a large application that I wrote using Excel and VBA for work, but I am stuck on one stupid little thing.

My problem is that I need to be able to determine the number of used rows in a WorkSheet (i.e. rows that contain at least one cell of data). Logically, I thought that the following line of code would get me this result:

	VB:
	
 
dblNumUsedRows = ActiveSheet.UsedRange.Rows.Count 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, this code does not work for me. I have 406 rows containing data on the active sheet, but this code is returning dblNumUsedRows = 12605.

Weird!

Can anyone help me out? I would greatly appreciate it!

Thanks!
Bryan

I am currently using this macro to remove all blank rows from my worksheet. Since I am already finding the LastRow, is there a way I can modify this to also delete the last 2 rows that contain information in column B?


	VB:
	
Dim LastRow As Long, r As Long 
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row 
For r = LastRow To 1 Step -1 
    If IsEmpty(Cells(r, 1)) And IsEmpty(Cells(r, 2)) Then Rows(r).Delete 
Next r 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks
D0M

Hi all,

I am using the code below to delete rows which contain, in this example, SEOP. Where it says Value "SEOP", what can I use so it deletes values NOT CONTAINING SEOP? The text in a cell may say SEOP Germany so it needs to look for which have SEOP in the text.

Note the part of the below code where it says VALUE SEOP,

What can i put inplace of to make it look for cells containing??

Thanks,

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "B").Value) Then
l

ElseIf .Cells(Lrow, "B").Value "SEOP" Then .Rows(Lrow).Delete

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

Hi there, I am needing to compare 2 columns from Sheet1 with 2 columns on Sheet2 and display a message when both columns match. I have this working somewhat, but my problem is that I have multiple instances of the data that's in the columns. So, if column A matches anything on Sheet2 and column H matches anything I get a match. I need to only show a match when the two columns match "together". I need this in a macro that automatically runs upon change event instead of formula. It will be used in a template by several beginner level Excel users.

I'm currently using named ranges "manu" and "subp" from Sheet2 for the match.

Here's my sample code for the change event:


	VB:
	
 Range) 
    Set rTarget = Target 
    Dim totalrows As Long 
    totalrows = ActiveSheet.UsedRange.Rows.Count 
    hrow = "$H$2:$H$" & totalrows 
     
     
    If Not Intersect(Target, Range(hrow)) Is Nothing Then 
        On Error Resume Next 
         
        Application.EnableEvents = False 
        Run "Exemptions" 
         'Turn events back on
        Application.EnableEvents = True 
         'Allow run time errors again
        On Error Goto 0 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here's the code for the macro itself:


	VB:
	
 Range 
 
Sub Exemptions() 
    Dim c As Range 
     
    For Each c In rTarget 
         
        If Not Range("manu").Find(c.Offset(0, -7).Value, LookAt:=xlWhole) Is Nothing Then 
            If Not Range("subp").Find(c.Value, LookAt:=xlWhole) Is Nothing Then 
                 
                MsgBox "Check for Possible Lead Exemption" 
                 
                 
                 
            End If 
        End If 
    Next c 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated.

Hi There,

I am very confused about my macro I wrote which has data similar like this:

Number/ Name/ Amount1/ Amount2/ Amount3

100 XAOO 10 5 1
101 XAOB 20 10 2
100 XAOO 30 15 3
102 XAOC 40 20 4
102 XAOC 50 30 5

and it should be like this at the end:

Number Name Amount1 Amount2 Amount3

100 XAOO 10 20 4
101 XAOB 20 10 2
102 XAOC 90 50 9

So I thought I was done with this macro because it is working until I realised it doesnt add the last 3 rows to the consolidated list.

My original data comes from differentsheets and being organised in a seperate temporary sheet which is called "Notinuse3". So this macro runs with this tempo sheet. I run the macro with different data and the last try showed me while it is clearing content (which are the duplicated rows) it always does not iclude the last couple of rows. Here is my code, please check this out.


	VB:
	
 
Dim rInput, lastrange As Range 
Dim oDic As Object 
Dim nTotal(), vInput() 
Dim i2 As Long, j, k, lastrownumber As Long 
 
With Application 
    .ScreenUpdating = False 
    Sheets("NotINuse3").Activate 
    Set rInput = Range("A1", Range("E" & Rows.Count).End(xlUp)) 
    vInput = rInput.Value 
    Redim nTotal(1 To UBound(vInput, 1), 1 To 5) 
    Set oDic = CreateObject("Scripting.Dictionary") 
    With oDic 
        For i2 = 1 To UBound(vInput, 1) 
            If Not .exists(vInput(i2, 1)) Then 
                j = j + 1 
                For k = 1 To 5 
                    nTotal(j, k) = vInput(i2, k) 
                Next k 
                .Add vInput(i2, 1), j 
            ElseIf .exists(vInput(i2, 1)) Then 
                For k = 3 To 5 'Starting from K=3 because we don't want to add up first 2 rows
                    nTotal(.Item(vInput(i2, 1)), k) = nTotal(.Item(vInput(i2, 1)), k) + vInput(i2, k) 
                Next k 
            End If 
        Next i2 
    End With 
    lastrow = ActiveSheet.UsedRange.Rows.Count 
    Set lastrange = Range("A2:E" & lastrownumber) 
    lastrange.Clear 
     ''''''''''''Columns("A:E").Clear   (this one also does the same)
    Range("A2").Resize(j, 5).Value = nTotal 
    Application.ScreenUpdating = True 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Thank you so much for all the help !

Hello,

I have a worksheet where a user will copy data into column A, starting in row 6.
This data may or may not have empty rows between the data. The data string will be comprised of numbers and letters (i.e. ab7sf63-sdg-fj89b/1.2.840.1.111.dts)
I need to remove the extra blank rows, and count the number of entries containing the string "dts" and count the number of entries NOT containing .dts.
The number of rows entered by the user varies.
This is the code I have:

	VB:
	
 DeleteEmptyRows() 
     
     'Find the last row containing data
    Dim LastCell As String 
    LastCell = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Address 
     
     'Clear the previous results
    Range("C4:D4").Clear 
     
    LastRow = ActiveSheet.UsedRange.Row - 1 + _ 
    ActiveSheet.UsedRange.Rows.Count 
    Application.ScreenUpdating = False 
    For r = LastRow To 1 Step -1 
        If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete 
    Next r 
     
    Range("C4").Select 
    ActiveCell.FormulaR1C1 = "=Countif(A:A, ""*""&dts&""*"")" 
     
    Range("D4").Select 
    ActiveCell.Formula = "=Countif(a6:LastCell, "  "&" * "&c3&" * ")" 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So far, my first formula entry posts the formula: " =COUNTIF(a : (a), "*"&dts&"*") "
This reference is obviously incorrect. I've changed my quotation marks around, but still cannot get the correct formula to post.
What am I missing?
Will the same apply to the next formula?

Thank you so much!

Hi all,

Hope the title does enough to get some help on the problem that I am facing...

What I need to be able to achieve is a function in my macro that will

a: Count the number of rows in the active sheet
b: Allow me to use this info in other functions, such as subtotals, autofills

After searching many sites I'm pretty certain the function I need to use is "rowcount".

I've managed to incorporate a loop using this function, which is fine when running the macro but not so good when you are stepping through 5000+ rows


	VB:
	
RowCount = ActiveSheet.UsedRange.Rows.Count 
For x = 2 To RowCount 
    Cells(x, 42).Select 
    ActiveSheet.Paste 
Next x 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So for me now it seems as though "x" should represent the number of row that has been counted in the above code. But when I try to use "x" in functions the macro falls over


	VB:
	
Range("AQ2:AX2").Select 
Selection.AutoFill Destination:=Range("R2C43:RxC50") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Hope this is clear and thanks in advance

I have searched the forums for how to count unique records, and I can only find formulas, and not code. This code works fine, and I get the right "actual" count, but I need to change it to a unique values count. Please help!

	VB:
	
 CountEmployees() 
    lastrow = ActiveSheet.UsedRange.Rows.Count 
    For t = lastrow To 2 Step -1 
        If Cells(t, 8).Value  "" And Cells(t, 8).Offset(2, 4).Value  "" Then 
            Cells(t, 12).Select 
            Selection.Offset(1, 0).Select 
            Range(Selection, Selection.End(xlDown)).Select 
            List = Selection.Cells.Count ' Need to make this unique count
            Cells(t, 9).Value = List 
        End If 
    Next t 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


If I use the macro:


	VB:
	
Columns("A:A").Select 
Selection.Insert Shift:=xlToRight 
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[2],RC[3])" 
Selection.AutoFill Destination:=Range("A1:A19"), Type:=xlFillDefault 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
then this of course works for up to 19 rows but...

How do I use ActiveSheet.UsedRange.Rows.Count INSTEAD OF A19 to load JUST the active non-null cells?

Paul

Hi,

I have about 20 Excel files in which the actual data is in alternate rows. For example:

In file 1 the 1st, 3rd, 5th and so on rows contain data while in file2 the 2nd, 4th, 6th and so on rows contain data. Many of the files also have data in th 1st row and then after skipping 3 rows (for example) the next row containing data is the 5th row.

Assuming the above is clear, I am looking for a code which I can invoke while my active cell is A1. The code should ask the starting row containing data and the number of blank rows between rows containing data...

Given my little VBA knowledge, I have come up with the following pseudo-code and will appreciate help/guidance from someone.

Sub DelAlternateRows( )

Dim LastRow As Long, r As Long

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

For Each r = 2 to LastRow step 2
' select the 2nd row assuming that the code was invoked when the active cell was A1
' and the assumption that the first row contains data

' Delete the selected row
Next r
End Sub

I know the code is not even close to handle all the cases that I have mentioned and thus this request for help.

Newbie here again. I want to delete rows based on a formula and have studied the many methods on this board. It's fair to say I can't figure out some of the code and the experienced people seem to be merging many steps into one to be efficient.

I choose the following code to start with:


	VB:
	
 DeleteRowsWithSpecifiedData() 
     'Looks in Column D and requires Column IV to be clean
    Columns(4).EntireColumn.Insert 
     
    With Range("D1:D" & ActiveSheet.UsedRange.Rows.Count) 
        .FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Not Needed"",NA()))" 
        .Value = .Value 
        On Error Resume Next 
        .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete 
    End With 
    On Error Goto 0 
    Columns(4).EntireColumn.Delete 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Couple of points of confusion
1) I figured out the RC[1] is an offset column on the same row. But I couldn't find documentation on it. what book(s) do you personally recommend as I try to follow and figure out the various code samples on this site?

2) I want to change the formula to be an if statement where the test condition is an AND function testing for specific cells to be equal to zero. They must each be zero, not a sum, or a range check as they are not contiguous..
Original formula

"=IF(RC[1]="""","""",IF(RC[1]=""Not Needed"",NA()))"

I tried a couple of variations of things like
=if(and(RC[13]=0, RC[17]=0, RC[22]=0),"""",NA())

to no avail, I get errors on the comma before the NA. (Sorry, can't remember the error code at the moment)

I understand the NA is the special code the delete statment is looking for, so I want to test for the condition, and set the NA in the same If statement. I can't figure out why my statement doesn't work. Oh yeah I also tried =if(worksheetfunction.and(etc and got the same error.

Finally, what are the rules as to where you can "break" a line of code? when do you put the underscore and when do you not?

Guidance is appreciated, and book or training recommendation focused on Excel VB would be outstanding.

Dane Strickland

Hi

I keep get compiler error wonder if someone can help.
If checkbox true search column J for "DIV" and hide remaining rows within range (85:lastrow)


	VB:
	
 CheckBox1_Click() 
     'Show_DIV
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    Dim lastrow As Long, r As Long 
    Dim i As Integer 
    ActiveSheet.Name = "P&L SUMMARY" 
    lastrow = ActiveSheet.UsedRange.Rows.Count 
    For r = lastrow To 1 Step -1 
        For i = 1 To r 
             
            If CheckBox1 = True Then 
                If Cells(i, 10) = "" Or UCase(Cells(i, 10)) = "DIV" Then Cells(i, 1).EntireRow.Hidden = False 
                 
            Next i 
        Else 
            If CheckBox1 = False Then 
                If Cells(i, 10) = "" Or UCase(Cells(i, 10)) = "DIV" Then Cells(i, 1).EntireRow.Hidden = True 
            End If 
        Next i 
        Application.Calculation = xlCalculationAutomatic 
        Application.ScreenUpdating = True 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks
lee


No luck finding an answer? You could always try Google.