Free Microsoft Excel 2013 Quick Reference

VBA Selection Count

Good morning all,

I'm trying to count the number of rows in a selection
Code:
Am I missing something from this?

Later in the code I do this..
Code:
Range("A" & lngLastRow2).Select
    ActiveSheet.Paste


Post your answer or comment

comments powered by Disqus
Hey guys, I'm New to VBA and looking for a few pointers. This is the first code I have ever wrote, with no coding experience, so forgive me if its a little rough but I think I did ok.

Below is the code to calculate the sum product of four different concentrations. When I run the code it will locate the correct sheet, run down through the dates, select the correct date and calculate the 4 separate sum product calculations correctly. My two problems are as follows..

1) I want to not only locate the date but the time on that date as well. (I need hourly calculations)
2) When I change the amount in the "Gallons Used" column so that I can run a new calculation at a different date, the data will change in any location where the calculation was run prior.

Attached is a dummy data file with most dates removed and all of the worksheets besides two removed so that the file will fit.


	VB:
	
 PaintUsage() 
     
     
     'Defining all variables
    Dim SpeciationTableCell, BoothACell, BoothBCell, BoothCCell, BoothDCell, BoothECell As Range 
    Dim PaintDate As Date 
    Dim rng As Range 
    Dim GallonsUsed, Density, VOCHAP, HAP, ES, PM, SP_VOCHAP, SP_HAP, SP_ES, SP_PM As Integer 
    Dim i, x As Integer 
    Dim BoothA, BoothB, BoothC, BoothD, BoothE, Time, VOCHAPTotal, HAPTotal, ESTotal, PMTotal As String 
    Dim LastRow As Long 
    Dim Jobcounter As Integer 
    Dim FindString As Date 
     
     'Making the SpeciationTable Active
    Set SpeciationTableCell = Sheets("SpeciationTable").Range("A13") 
    Sheets("SpeciationTable").Activate 
    SpeciationTableCell.Select 
     
     
     
     'Assigning values to all variables on sheet 1 this is our first priority loop, it will continue until their is not a
value listed for Booth
    Booth = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 0).Value 
    PaintDate = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 1).Value 
    Time = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 2).Value 
    GallonsUsed = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 3).Value 
    ProductName = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 6).Value 
    Density = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 8).Value 
    VOCHAP = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 10).Value 
    HAP = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 11).Value 
    ES = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 12).Value 
    PM = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 13).Value 
    SP_VOCHAP = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 14).Value 
    SP_HAP = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 15).Value 
    SP_ES = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 16).Value 
    SP_PM = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 17).Value 
    FindString = Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 1).Value 
     
     'Selecting the Correct Booth & Date, then running calculation
     'Booth A
    If Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 0).Value = "BoothA" Then 
        With Sheets("BoothA").Range("A13:A8000").Offset(Jobcounter, 0) 
            Set rng = .Find(What:=FindString, _ 
            After:=.Cells(.Cells.Count), _ 
            LookIn:=xlFormulas, _ 
            LookAt:=xlWhole, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
            If Not rng Is Nothing Then 
                Application.Goto rng, True 
            Else 
                MsgBox "Nothing found" 
            End If 
        End With 
        rng.Offset(Jobcounter, 2).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!k13:k42)" 
        rng.Offset(Jobcounter, 3).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!l13:l42)" 
        rng.Offset(Jobcounter, 4).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!m13:m42)" 
        rng.Offset(Jobcounter, 5).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!n13:n42)" 
    End If 
     
     'Booth B
    If Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 0).Value = "BoothB" Then 
        With Sheets("BoothB").Range("A13:A8000").Offset(Jobcounter, 0) 
            Set rng = .Find(What:=FindString, _ 
            After:=.Cells(.Cells.Count), _ 
            LookIn:=xlFormulas, _ 
            LookAt:=xlWhole, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
            If Not rng Is Nothing Then 
                Application.Goto rng, True 
            Else 
                MsgBox "Nothing found" 
            End If 
        End With 
        rng.Offset(Jobcounter, 2).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!k13:k42)" 
        rng.Offset(Jobcounter, 3).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!l13:l42)" 
        rng.Offset(Jobcounter, 4).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!m13:m42)" 
        rng.Offset(Jobcounter, 5).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!n13:n42)" 
    End If 
     
     'Booth C
    If Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 0).Value = "BoothC" Then 
        With Sheets("BoothC").Range("A13:A8000").Offset(Jobcounter, 0) 
            Set rng = .Find(What:=FindString, _ 
            After:=.Cells(.Cells.Count), _ 
            LookIn:=xlFormulas, _ 
            LookAt:=xlWhole, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
            If Not rng Is Nothing Then 
                Application.Goto rng, True 
            Else 
                MsgBox "Nothing found" 
            End If 
        End With 
        rng.Offset(Jobcounter, 2).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!k13:k42)" 
        rng.Offset(Jobcounter, 3).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!l13:l42)" 
        rng.Offset(Jobcounter, 4).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!m13:m42)" 
        rng.Offset(Jobcounter, 5).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!n13:n42)" 
    End If 
     
     'Booth D
    If Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 0).Value = "BoothD" Then 
        With Sheets("BoothD").Range("A13:A8000").Offset(Jobcounter, 0) 
            Set rng = .Find(What:=FindString, _ 
            After:=.Cells(.Cells.Count), _ 
            LookIn:=xlFormulas, _ 
            LookAt:=xlWhole, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
            If Not rng Is Nothing Then 
                Application.Goto rng, True 
            Else 
                MsgBox "Nothing found" 
            End If 
        End With 
        rng.Offset(Jobcounter, 2).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!k13:k42)" 
        rng.Offset(Jobcounter, 3).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!l13:l42)" 
        rng.Offset(Jobcounter, 4).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!m13:m42)" 
        rng.Offset(Jobcounter, 5).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!n13:n42)" 
    End If 
     
     'Booth E
    If Sheets("SpeciationTable").Range("A13").Offset(Jobcounter, 0).Value = "BoothE" Then 
        With Sheets("BoothE").Range("A13:A8000").Offset(Jobcounter, 0) 
            Set rng = .Find(What:=FindString, _ 
            After:=.Cells(.Cells.Count), _ 
            LookIn:=xlFormulas, _ 
            LookAt:=xlWhole, _ 
            SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, _ 
            MatchCase:=False) 
            If Not rng Is Nothing Then 
                Application.Goto rng, True 
            Else 
                MsgBox "Nothing found" 
            End If 
        End With 
        rng.Offset(Jobcounter, 2).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!k13:k42)" 
        rng.Offset(Jobcounter, 3).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!l13:l42)" 
        rng.Offset(Jobcounter, 4).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!m13:m42)" 
        rng.Offset(Jobcounter, 5).Formula = _ 
        "=SUMPRODUCT(SpeciationTable!d13:d42,SpeciationTable!i13:i42,SpeciationTable!n13:n42)" 
    End If 
End Sub 

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


Hello All,

I'm at the amiture level in VBA coding.

I have a selection of cells that I need to find how many are less than an InputBox entry. I have the selection and the InputBox working but I can't figure out the COUNTIF.

Here's what I have so far:

	VB:
	
Range("B2").Select 
FirstRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row 
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 
Range("B2").Resize(FirstRow, LastColumn - 1).Select 
ttlClls = Selection.Count 
Message = "Enter minimum useable signal level." 
Title = "Identify sites with poor signal levels" 
minSig = InputBox(Message, Title) 
dffClls = "=COUNTIF(Selection, " < "minSig)" ' This line isn't working.

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

Guys,

I need some vba code to
1)count along a row the number of VISIBLE populated cells and
2) If the number of visible cells is = 13 then it will
3)Hide the 2nd columns data
I tried using this just for the count mechanism and testing by manually hiding the 2nd column, Range(b:b) at that time but the count cannot pass the hidden cell despite the special cells code.

so at the moment i am playing around with the following


	VB:
	
 
ActiveSheet.UsedRange.Select 
ColcCount = Selection.SpecialCells(xlCellTypeVisible).Columns.Count 
If ColCount = "13" Then 
    Range("A1").Offset(0, 1).Select 
    Selection.EntireColumn.Hidden = True 
End If 

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

The logic seems right, but its probably something really really simple for you guys!!!
Thanks in advance

may i know how many empty cells
are there between a certain range that
a vba can count ?

my code cannot do it seems the for loop
over do it

Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
For a = 2 To sheet1lineno
Selection.FindNext(After:=ActiveCell).Activate
Row = ActiveCell.Row
row2 = Row - 1
Range("U" & Row).Value = "=T" & Row & "/T" & row2
Next

Dear esteemed e-xcel-perts,

I'm trying to adapt a bit of code that I've borrowed off another helpful site (http://www.ozgrid.com/VBA/count-of-list.htm) to my own VBA app, but this sample code is giving me an error(*) despite some of the editing I've done.

*Here's where it gets tricky: I'm working in France where---go figure---excel is in french. This means all formulas that you're used to (i.e. sum, countif, vlookup, etc) are in their french translations, and instead of using a period to separate decimals, they use a comma. How practical, I know. The VBA is still english which gives me some hope, though error messages are en franÁais which makes debugging frustrating. My best translation of the error message is: "Compilation Error: Conditional Argument" I could be completely misleading you, though.

It highlights the first line of my code, while selecting a line in the middle. Here's what I'm working with:

Code:
Sub createCountOf()
'^^this line is highlighted 

Dim strHead As String
Dim strSheetName As String
Dim strListAddress As String

    strHead = Selection.Cells(1, 1)
    strSheetName = "'" & ActiveSheet.Name & "'!"
    strListAddress = Selection.Address(ReferenceStyle:=xlR1C1)

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=strSheetName & strListAddress).CreatePivotTable ,
TableDestination:="", TableName:="CountOf", DefaultVersion:=xlPivotTableVersion10
'^^this line is selected
        ActiveSheet.PivotTables("CountOf").AddFields RowFields:=strHead
        
        With ActiveSheet.PivotTables("CountOf").PivotFields(strHead)
            .Orientation = xlDataField
            .Caption = "Count of" & strHead
            .Function = xlCount
        End With
  
        ActiveWorkbook.ShowPivotTableFieldList = False
        Application.CommandBars("PivotTable").Visible = False
  
End Sub
I hope I'm not confusing anyone and if so I apologize. Please don't hesitate to let me know if you need more info!

Thanks in advance for any help!

Marc

Is there a way in VBA to count the number of columns holding the filter symbol?
I currently have a Reset macro to remove all filters in one go:
Code:
and cycle through this a fixed number of times.  Some of my sheets may only have 4 columns filtered and some could have as
much as 18 (although the're not all aplied at once).
It would be helpful to know one way or the other if this is possible.
Thanks in advance.

Hi guys,

I'm currently writing a word VBA and I have ran into problems.

My macro is supposed to search for the word "Forms" and select all the text until it reaches the word "Mixing".

Attached is a image and my source code, I'm new to VBA and would appreciate it if someone could help me.

Thanks


	VB:
	
 CommandButton1_Click() 
     
     'find the word "forms"
    Selection.HomeKey 
    Selection.Find.ClearFormatting 
    With Selection.Find 
        .Text = "forms" 
        .Replacement.Text = "" 
        .Forward = True 
        .Wrap = wdFindContinue 
        .Format = False 
        .MatchCase = False 
        .MatchWholeWord = False 
        .MatchWildcards = False 
        .MatchSoundsLike = False 
        .MatchAllWordForms = False 
    End With 
    Selection.Find.Execute 
     
     
End Sub 
 
Private Sub CommandButton2_Click() 
     'select until "Mixing" is found
     
    While Selection.Find.Text  "Mixing" 
        Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend 
    Wend 
     
End Sub 

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


Hi I'm new here, and very new to VBA. Im am writing a macro to automatically get information from gauges connected via rs232c with help from a excel add-in that provides worksheet functions to allow the gauges to be read. My problem is that i need to get the column letter from a column count, then write this letter to a cell. You can see below where i have commented what variable holds the column count and where i'd like it put. Any help would be much appreciated.

Sub Oedometer()
   
    ' defines loads on each oedometer
    str_loada = InputBox(Prompt:="Enter Loading for Oedometer A(kPa)", Title:="Load Amount")
    str_loadb = InputBox(Prompt:="Enter Loading for Oedometer B(kPa)", Title:="Load Amount")
    
    ' sets up sheet for oedometer A
    Sheets("Sheet1").Select
    i_colcounta = ActiveSheet.UsedRange.Columns.Count
    i_cola = i_colcounta + 1
    Cells(1, i_cola).Value = str_loada
    
    ' sets up sheet for oedometer B
    Sheets("Sheet2").Select
    i_colcountb = ActiveSheet.UsedRange.Columns.Count
    i_colb = i_colcountb + 1
    Cells(1, i_colb).Value = str_loadb
    
    MsgBox "Click OK to begin data collection."
    
    ' collect data
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 10      ' time interval
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
   
    Sheets("Sheet3").Select
    Range("B3").Select
    ActiveCell.Value = i_cola    '  place column letter here
    Range("A2:A6").Select
    Application.Run Macro:="Easy_Macro"
    
    
End Sub


Hi,

First of all, like so many others, I'm not very good in VBA .
I'm trying to come up with a code that would select the 6th prior to the last cell contaning a value with a row. I have variable number of data per row, but I was able to go around this problem by using:


	VB:
	
Range("A1").End(xlToRight).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, now, I'm trying to copy the value that is in the 6th cell before the last one, and for some reasons, I cannot figure out how to do it.
Does anyone have a good idea ?

Thank you very much for your help,

Hi all,

I have the following vba code:

PHP Code:
If ActiveCell.Value = "" Then

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-10]=""10"",VLOOKUP(RC[-14], Table!R5C1:R511C9,8),IF(RC[-10]=""90"",VLOOKUP(RC[-14], Table!R5C1:R511C9,9),""""))"
   End If
   If ActiveCell.Value = "" Then

ActiveCell.FormulaR1C1 = _
        "=IF(RC[-10]=""1X"",VLOOKUP(RC[-14], Table!R5C1:R511C9,8),IF(RC[-10]=""9X"",VLOOKUP(RC[-14], Table!R5C1:R511C9,9),""""))"
   End If 
My problem is that the part of the code that refers to the pivot table on the separate worksheet (Table!R5C1:R511C9) changes with every report I analyze, some tables having 511 rows others have 900 rows.
is there a way of making this code simpler?

And most importantly what should I write so that excel automatically select the entire pivot table ??

Thanks in advance!

Hi!

I have been doing some VBA and have forgotten some of the basics

I tried using the following code to count the number of entries in a column (Column A, text entries) and then display it as a percentage of the total in a separate box. I just got the result "1".


	VB:
	
 Count_Selection() 
    Range("a1").Activate 
    Dim cell As Object 
    Dim count As Integer 
    count = 0 
    For Each cell In Selection 
        count = count + 1 
    Next cell 
    Range("g1").Value = count 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If there could be any help available, I would be extremely grateful.

/A

I need help with the VBA syntax to specify the selection of a certain number of cells in a specific direction from any given starting point.

In other words, I need to create 3 different macros. All of them will perform the same function, in 12, 25 or 51 cells. As a part of the macro, I need to know how to have VBA select either 12, 25 or 51 cells to the right of wherever my cell selector happens to be when I run the macro.

Hi All,
I am trying to from Excel VBA using an ADO connection to an Access Db, execute an SQL COUNT command and capture the number from the COUNT into a cell. I am able to run the procedure without any errors, but the COUNT value does not come back in Excel. Any help would be greatly appreciated. My code is below:


	VB:
	
 DbReport() 
    Dim cnt As ADODB.Connection 
    Dim rst As New ADODB.Recordset 
    Dim stSQL As String 
    Dim stCon As String 
    Dim stAccessPath As String 
    Dim rngCell As Range 
     
    stAccessPath = "C:Deliveries.mdb" 
     
     'Indicates database filepath
    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & stAccessPath & ";" & "Jet OLEDB:Database;" 
     
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
     
     'Establishes connection
    cnt.Open stCon 
     
    For Each rngCell In Range("Cities") 
        On Error Resume Next 
        stSQL = "SELECT COUNT(*) FROM DeliveryList WHERE City = '" & rngCell.Value & "'" 
        With rst 
            .Open stSQL, cnt, adOpenDynamic 
            ActiveSheet.rngCell.Offset(0, 1).CopyFromRecordset rst 
            .Close 
        End With 
    Next rngCell 
    On Error Goto 0 
     
End Sub 

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


Hi Dave and those that can help.

Previously i have asked u for help in counting:
1. No. of rows
2. No. of columns
3. No. of numeric cells
4. No. of alphanumeric cells
5. No. of alphabetic cells
6. No. of NULL cells
The below are what i have used using excel meaning the results are shown in excel file format (displayed in cells):
1. COUNTA(Table!A:A))
2. COUNTA(Table!1:1))
3. COUNT(Table!B2:Z60000))
4. COUNTIF(Table!B2:Z60000,"*"))
5. CountAlphas(Table!B2:Z60000)) --> code below


	VB:
	
 
    Dim cell As Range 
    Dim iCtr As Long 
     
    For Each cell In CountRange 
        If Not IsEmpty(cell) Then 
            If Not cell.Value Like "*[0-9]*" Then 
                iCtr = iCtr + 1 
            End If 
        End If 
    Next 
    CountAlphas = iCtr 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
6. COUNTBLANK(Table!B2:Z60000)

However the biggest problem now is that i have to switch from displaying in excel to using vba so that the user interface using my program would look "nicer". I have created userforms that look like menus for the program, but i am unable to use the above coding as they are not in vba codes. What i need now is them to be in vba codes and to display the results in msg boxes thus enabling the program to look more professional. As i am totally new to excel vba, i can only hope that those that are more proficient in it can help out by posting the solutions. Thanks in advanceD!

What I am trying to do is to have a combo box which displays the sheet names from the current file, when a person selects a name from the combo the sheet is selected.

I have created the combo using the Forms toolbar. The combo has been populated using the following code:

Sub getsheetnames()
Dim number, counter,
Dim indexnumber

On Error GoTo error:

Application.ScreenUpdating = False
Worksheets(1).Select
Range("A10:A20") = ""
Range("A9").Select
number = ActiveWorkbook.Sheets.Count
For counter = 1 To number
sheetname(counter) = Worksheets(counter).Name
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = Worksheets(counter).Name
Next counter
error:
Range("A2").Select
On Error Resume Next
indexnumber = Range("B10")
Sheets(indexnumber).Select
Range("A2").Select
Application.ScreenUpdating = True

End Sub

All the above works, the combo is populated with the sheet names.

The Problem: how do I get the actual sheet to be selected when the user makes a selection from the combo?

Any suggestions much appreciated.

MZP

Hello,

I thought this would be easy but it's proving tougher than I thought...

I have a 3000 row spreadsheet in Excel 97. My VBA code has identified the last row as EndRow. I now need to select every fourth row and copy it to the clipboard ready for pasting into a new workbook.

I've tried using a For...Next loop with a step in it, but it selects every fourth row and then unselects it and moves on to the next. I guess I want to recreate holding down CTRL while selecting multiple rows.

Help me, my hair is coming out in clumps!

Many thanks,

Cunners

I am so close on this one. I have a macro that will insert a row at the top, then use a formula to retrieve information from the row below it, then sort the columns based on this retrieved dates, then delete the inserted row.

Here is what I have (extra stuff for now, just for me to keep track of things), and everything works except the one line (in bold).


	VB:
	
 
 
Sub SortMacro() 
     
    Dim Rng As Range 
    Dim myColm As String 
    Dim n As String 
    Dim m As Double 
    Dim myCount As Integer 
    myColm = InputBox("Enter Column Letter") 
     
     
    Application.ScreenUpdating = False 
     
    m = Range("A1", Range("A65536").End(xlUp)).Rows.Count 
    myCount = Range("F1", Range("IV1").End(xlToLeft)).Columns.Count 
    MsgBox myCount 
    Rows("1:1").Insert Shift:=xlDown 
    If myColm  "" Then 
        n = myColm 
        Set Rng = Range(n & "1", Range("IV1").End(xlToLeft)) 
        Range(n & "1").Select 
         
        ActiveCell.FormulaR1C1 = "=DATEVALUE(RIGHT(R[1]C,11))" 
         
        Selection.AutoFill Destination:=Range(n & "1", Range("IV1")), Type:=xlFillDefault 
        Range(n & "1", Range("IV1").End(xlToLeft)).Select 
         
        [b]        Columns(1, n).Select[/b] 
         
         
        Selection.Sort Key1:=Range(n & "1"), Order1:=xlAscending, Key2:=Range(n & "3") _ 
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=4, MatchCase:= _ 
        False, Orientation:=xlLeftToRight, DataOption2 _ 
        :=xlSortNormal 
        Rows("1:1").Delete Shift:=xlUp 
        Range("E1").Select 
    Else 
        Exit Sub 
    End If 
    Application.ScreenUpdating = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
On the bold line, I want to select all the columns that are used from the input column (let's say column F - which the person will enter into Input box) to the end of the table. In the present file it is 107 columns (F:DH). How can I select those columns? Each time the starting column and the ending column will change.

Any help appreciated. (Unfortunately I am leaving work, but will check later tonight)

I know that in VBA you can select a range by doing this:

Range("A16:I16").Select

But I am using a loop that adds 2 to the row index that I created. I tried:

Range("A(rwIndex):I(rwIndex)").Select

But that produced an error. What other way can I do this?

Thanks,
Nicole

Howde friends

does anyone know the best way to create a vba script that will allow me to click an icon on the toolbar which would then produce a little box and allow me to select say a picture from a selection and then place it in the worksheet or document? am trying to automate a set of sequences...

any ideas?

Hi,

I've made a quite simple work-calendar & now I have made some buttons for each type of holiday. Depending on wich button you click, the selected active cells will be coloured in the respective colour.
Now, that works & it's not that hard.

But the real problem I have is one I can't seem to fix.

If I make a User Defined Function to sum & Count like the one explained here (http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm) I get an error each time I try to insert the formula (=ColorFunction($C$1,$A$1:$A$12,FALSE) into the formula bar.

The error message tells me that the formula has an error in it and it asks me if I tried to put in a formula or not. If not I have to leave the '=' away.
Now, IT IS a formula so I don't know what I'm doing wrong...

I've uploaded the excel file to my own server because it exceeded the filesize overhere.
This is the link --> http://www.dj-jug.be/calendar-example.xls

I Hope someone can help me.

Greetzzz

I have a Pivot Table that is refreshed daily - the date is a Page field in the format 20050822.

What I would like to be able to do within the VBA code that I use to refresh the data is to select the maximum value (most recent date) of this field before saving and distributing the file.

Many thanks for any suggestions

Nick

Now, I know this must be really obvious, but the only solutions I'm coming up with take up telephone books of code:

How does one refer to a subsection of a named range? For instance, if I want to select only rows 5 to the end of a dynamically-updated range, how would I do that in the code?

What I used a moment ago was: Range(Range("rng").Offset(5, 0).Cells(1, 1), Range("rng").Cells(Range("rng").Rows.Count, 1)).Select

But I'm betting there's a more guru way to do this....

Thanks much in advance - !

Hi..

I'm trying to run this query in excel vba.. although all my other queries worked fine this one doesn't return anything. This query is a little more complex than the other queries i have but i dunno if it's causing this. I ran the same query in Microsoft Query in excel and it worked fine but when i put this in vba it doesn't seem to work...Any help would be great..

Here is my vba code along with the sql:

Code:
SQLExecQuery Ers, _
    "SELECT Count(ERSS.ID)  " & _
    "FROM ers2006.dbo.ERSS ERSS  " & _
    "WHERE (ERSS.ID In (SELECT ERSS.ID  " & _
    "FROM ers2006.dbo.ERSS ERSS  " & _
    "WHERE (ERSS.ACAD_PLAN In ('" & AcadPlan & "','" & AcadPlan2 & "')) AND (ERSS.STD_LEV In ('1','2','3','4')) AND
(ERSS.ENR_STATUS = '4') AND (ERSS.PERIOD Like '%4') AND (ERSS.YEAR = '" & Year & "'))) AND (ERSS.PERIOD = '20064')  "

SQLRetrieve Ers, Cells(6, 2 + j), , , False


Hi all,

I am trying to write some VBA code to select a range in a column going down to the last item in the column.

So I want to write a macro that will copy cells B4 - B17 into cells A4 - A17 but am having trouble with the code that will select B4 - B17. When I use the usual code which is as follows:

Code:
it selects down to A75, and I know why this is but not how to fix it.

This is raw data extracted from a third-party system. It has an option to export to Excel, but where it appears to have blank cells Excel actually believes there is data in there.

So is a way to identify what data Excel thinks is in there and perhaps use this to be able to select the range I want?

Different extractions will need a different range moved over - however it will always start at B4 and go down to B-whatever.

Hope you can help and I can send an example spreadsheet if needed...

Thanks
Richard


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