How do I get a count from a range object 4

[SBendBuckeye]

I have used Access pretty extensively so am pretty familiar with VBA. I am doing much more Excel related work on my current project still using Access as an automation server.

My users have a standard Excel template that they use to request changes for a non Microsoft process. Valid columns have column names such as Name, Dept, Part#, etc. As part of the prevalidation of the Excel, I want to delete empty columns (eg no changes to data in this column).

I have created an Array of ColumnNames and want to spin through it one column at a time and delete those columns that only have 1 item in the column (the Column Name). I have already calculated the last row using SpecialCells to get it, so my code looks like this:

Dim rng As Range
Dim cl As Object
For lngCol = 1 To UBound(avarColumnNames)

'Define a range object for current column portion
Set rng = Range(Cells(1, lngCol), Cells(lngRow, lngCol)

'Get blank cells for the current range
rng.SpecialCells (xlCellTypeBlanks)

Next lngCol

A. Where do I find the cell count for the rng.SpecialCells statement? If it is equal to the number of rows in the column - 1 then the users entered not data into that column and it should be deleted. I think I am on the right track but something is missing. Can someone help me on this?

B. What is the best way to delete a column if it is not necessary? Is there a Columns collection somewhere in the worksheet item I can reference?

C. Previous to the above, I want to copy Sheet1 to Sheet2 and then if changes are made, I will rename Sheet2 to Original Sheet. How is the best way to do this in VBA. If I don't make changes how do I delete Sheet2?

D. I am used to spinning collections in Forms, QueryDefs, TableDefs, etc in Access. What are the major collections when working with Excel?

E. Is there an Office equivalent of Access Developers Handbook?

I'm sorry if the answers to these are obvious, but I remember beating my head against the wall with the same types of things learning Access and I would like to spare myself some pain if I can.

Thanks in advance for any help and/or suggestions!

Have a great day!

 

[Zathras]

Here is an answer for parts A and B. When I have more time, and if no one else jumps in here to help out, I will try to address your other questions.

Sub test()
  anames = Array("Name", "Dept", "Part#")
  DoCols anames, 100
End Sub

Sub DoCols(avarColumnNames, lngRow)
Dim rng As Range

With ActiveSheet
  For lngCol = .UsedRange.Columns.Count To 1 Step -1

'Get last unused cell in rng

    Set rng = .Range(Cells(lngRow, lngCol), Cells(lngRow, lngCol))
    Set rng = rng.End(xlDown)
    Set rng = rng.End(xlUp)

'If not row 1, then it doesn't matter

    If Right(rng.Address, 2) = "$1" Then

'It is row 1, if this is one of the listed columns, delete it

      If FindInArray(rng.Value, avarColumnNames) Then
        .Columns(lngCol).Delete
      End If
    End If
  Next lngCol
End With
End Sub

Function FindInArray(FindWhat As String, InArray As Variant) As Boolean
  For i = LBound(InArray) To UBound(InArray)
    If InArray(i) = FindWhat Then
      Find = True
      Exit Function
    End If
  Next i
End Function

 

[SBendBuckeye]

Thanks Zathras,

This is excellent stuff, just what I needed to get started. When I first started using Access, someone recommended to me the Access Developers Handbook which is about an 1800 page comprehensive technical reference for VBA developers.

Do you know of an equivalent for office VBA, or if not can you suggest something to me? If I had a comprehensive technical reference I would be able to follow things like you did above. As soon as I saw your code I could mostly follow it, but I would not have known for who knows how long about .UsedRange, etc.

I understand the concept of ranges in Excel, but the range object is a little bit confusing to me still. Once I have defined one, where do I look for the cell attributes (row, column, etc) that define a range. They appear to be part of the properties but do not show up in the locals window, is that accurate?

If I copy my code down to say Row 10 and start it over to the right say in Column D, the UsedRange returns the same number of columns. How do I get it to take into account the offset from $A$1?

Thanks again for your very significant help!

 

[Zathras]

UsedRange must be used with caution. The definition is hard to state, but it includes cells that are non-blank along with cells that have been individually formatted. Consequently if Columns A thru C and Rows 1 thru 9 are blank and unformatted, the UsedRange begins in D10, as you found out.

I employed UsedRange for your example because you indicated that your column headers were in row 1, and if more columns were examined, no harm would befall. But if more precision is required, then other techniques would be indicated.

You are also correct in your observation that understanding the Range object is key to success. The help file, while somewhat hit-or-miss, provides a wealth of information on the various properties and methods that pertain to the Range object. The other main object to study is the Worksheet object. It is there that you would discover the UsedRange property.

One thing to watch for is the distinction between the Cells property and the Offset property. At first glance they appear similar, but beware: rng.Cells(r,c) is a one-cell range within the larger rng range, but rng.Offset(r,c) is the same size as rng, displaced by r rows and c columns. E.g. if rng.Address is $A$1:$C$5 then rng.Cells(2,2) is essentially the same as Range("$B$2:$B$2&quot. On the other hand rng.Offset(2,2) would be the 15-cell range equivalent to Range("$C$3:$E$7&quot. And finally, if rng.Address is $A$1:$A$1, then rng.Offset(2,2) is essentially the same as Range("$C$3:$C$3&quot

There are a couple of books in the O'Reilly series that I found useful: "Excel 97 Annoyances" by Leonhard, Hudspeth and Lee (I don't know whether there is an update for 2K) and "Writing Excel Macros" by Steven Roman.

One final point. Excel has a macro recording facility that is sadly lacking in Access. Use it frequently! The results always need tweaking to replace hard-coded cell references, but the property-setting code is invaluable.

 

[SBendBuckeye]

Thanks again Zathras, maybe I'll just start referring to you as good ol' Woi (wealth of information). Many, many thanks for your helpfulness. There appears to be something not quite right with my help files as sometimes they work and sometimes they don't but since I bought the system used I don't dare try to reinstall for fear I couldn't.

You are correct about UsedRange and my needs. Since I had already written code to create an array by moving along row 1 until I hit vbNullString I just replaced UsedRange with my array and everything worked very well.

Why don't some of the collections like Columns and Rows show in the locals window when you are in debug mode? I looked and looked for them and couldn't find them and then your code shows me they were there the whole time.

If I understand you correctly, once you have set a range object up with range (cells, cells) then after that range.cells refers to the cells within the range relative to the top left. Do I understand you correctly?

Thanks again and have yet another star. At the rate you're going you could get tipmaster of the week right here in this thread.

Have a great weekend - you have surely helped mine greatly!

 

[Zathras]

I appreciate the feed-back. Always glad to help, and it's nice to know it is sometimes truly helpful.

As for moving along row 1 until you hit vbNullString, you may have noticed that I went from right to left instead. This makes deleting columns easier as you don't have to adjust your index every time you delete a column.

I don't use the locals window. (Although maybe I should take a look at it to see if it can be of any use to me. Thanks for the tip.)

The Cells property simply seems to be a way to access a single cell relative to the top left-hand cell of whatever range you are referencing. It doesn't even have to be one of the cells in the referenced range.

 

[RobBroekhuis]

Did you figure out the answers to your other questions? Briefly:

C. Previous to the above, I want to copy Sheet1 to Sheet2 and then if changes are made, I will rename Sheet2 to Original Sheet. How is the best way to do this in VBA. If I don't make changes how do I delete Sheet2?

sheets("Sheet1&quot.copy after:=sheets("Sheet1&quot
sheets("Sheet2&quot.name="Original sheet"
application.displayalerts=false
sheets("Sheet2&quot.delete
application.displayalerts=true

D. I am used to spinning collections in Forms, QueryDefs, TableDefs, etc in Access. What are the major collections when working with Excel?

One of the single-most useful help topics in Excel VBA is the one titled "Microsoft Excel Objects". It's the top of a tree-structured guide to all the objects you'll need in Excel VBA programming. The first place I go when I forget exactly how things are set up. You'll find all the collections there, including workbooks, sheets, worksheets, windows, names, commandbars....
Rob

 

[TMHill]

Rob

You get a big star for the comment on Microsoft Excel Objects. I've been coding VBA in xl for years and have never come across that! Thanks.

Tom

 

[xlbo]

Failing that - in xl VBE, press F2 and the object model will be displayed
Search for the item (eg range / combobox or whatever) and on the right hand side, it'll list all the properties, methods and events (if any) of the object Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[SBendBuckeye]

Great stuff all! Thanks!

Rob,

Not to quibble, but in your example above after you named sheet2, to delete it should you not use this code:

sheets("Original sheet&quot.delete

Also, I looked in 97 help and didn't find a Microsoft Excel Objects. Under Microsoft Excel there was a long list of things but no objects. Should I be checking Excel help or VBA help?

Have a great day!

 

[bryanbayfield]

VBA help.

I just use the object browser that Geoff refers to. The toolbar icon is a yellow unpacked box - you'll know what I mean when you see it!

 

[RobBroekhuis]

SBB,
I didn't mean my code above to be a continuous "program" - I was just giving code examples for copy/paste into the actual program, with appropriate logic applied. Sorry for the confusion.
Rob