Check Number of rows and insert category name

[sharc316]

Hi,

I would like a way to check for number of rows in a worksheet (Column A can be used) and then insert column B and populate it with a category name for each row.

Thank you for your help.

[mana]

category name?
I'd like a further explanation.

[Aussiebear]

I too am with Mana here. From where and how does the category get decided from?

[sharc316]

I can either specify the name or if possible to pull it from tab/sheet name.

[mdmackillop]

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) &" - " & CELL("Address")
or
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) & " - " & ROW()

[sharc316]

Thank you mdmackillop. Would you recommend inserting this formula using VBA? I'll be running this on multiple workbooks and worksheets. Also, i would need the code to lookup how many rows there are within each sheet and insert category name within those rows that contain data. I dont necessarily need the rows/address as the category name should be consistent in each row.

[mdmackillop]

I'm really unclear as to what you are after; maybe a sample workbook could show what you intend. You could easily record a macro to insert the formule (adjusted as required), either as formulae or fixed values. Give it a go and get back with any queries.

[sharc316]

I've spent some time today doing more research and testing and was finally able to find something that works. The only issue is that when for some statements where there is no data for a certain period of time it still inserts category name in B2. Do you know for a way to remedy this? Please see below.



DATE
CATEGORY
TYPE



Technology Incentives






Sheets("Technology Incentives").Range("B1").EntireColumn.Insert
With Sheets("Technology Incentives")
Set RngCol = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
End With
lastrow = RngCol.Rows.Count
Sheets("Technology Incentives").Range("B2:B" & lastrow).Value = "Technology Incentives"
Sheets("Technology Incentives").[B1].Value = "CATEGORY"

[mdmackillop]

Use SpecialCells to refine your range and omit non-blank cells

[sharc316]

Would you be able to show me how this is done?

[mdmackillop]

Have you tried the Help files?

Sheets("Technology Incentives").Range("B2:B" & lastrow).SpecialCells(xlCellTypeConstants).Value = "Technology Incentives"