Excel Macro

[wesleyw]

Okay I have an excel document with about 7000 rows.

 

Column A, Column B and Column C

 

In the A column it denotes a computer name in the B column it denotes a user who has access and Column C denotes the amount of users that have access.

 

However only on the first line for that computer in Column A is a value and I want to propagate the correct name to every row in Column A.

 

E.g.

 

[table=width: 500]

[tr]

[td]PC1[/td]

[td]User1[/td]

[td]3[/td]

[/tr]

[tr]

[td][/td]

[td]User2[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]User3[/td]

[td][/td]

[/tr]

[tr]

[td]PC2[/td]

[td]User1[/td]

[td]3[/td]

[/tr]

[tr]

[td][/td]

[td]User6[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]User9[/td]

[td][/td]

[/tr]

[/table]

 

Etc.. any ideas how I could code this?

[LosOjos]

I wrote this little macro a few years back for just that problem - you select the entire range of used cells in the column (not the whole column, otherwise the last entry will be repeated all the way to Excel's maximum row!) then run the macro:

 

Public Sub fill_blanks_above()
On Error Resume Next
Dim x As Range
For Each x In Range(Selection.Address)
   If x.Value = "" Then
       If IsNumeric(x.Offset(-1, 0).Value) Then
           If Left$(x.Offset(-1, 0).Value, 1) = "0" Or Left$(x.Offset(-1, 0).Value, 1) = " " Then
               x.Value = "'" & x.Offset(-1, 0).Value
           Else
               x.Value = x.Offset(-1, 0).Value
           End If
       Else
           x.Formula = x.Offset(-1, 0).Formula
       End If
   End If
Next x
End Sub

 

Thinking about it now, it wouldn't be difficult to add in a statement to break the loop if it goes past the last used row so that you could just highlight the column and hit run. I may just do that!

 

Also, "On Error Resume Next" is extremely bad practice for sloppy, lazy programmers like myself

Edited October 2, 2014 by LosOjos

[wesleyw]

Works exactly the way I need thanks!

[wesleyw]

I don't suppose you would have something that would copy a row to another sheet if say column B had a certain fill colour would you?

 

Wes

[LosOjos]

I don't suppose you would have something that would copy a row to another sheet if say column B had a certain fill colour would you?

 

Wes

 

Automatically do you mean? You can filter by colour in Excel (from at least 2007 onwards) and copy/paste that way, but if it needs to be an automatic thing then it'll get a bit trickier... I guess you'd need to have the sheet 'refresh' at an interval (or add a button) and essentially do the filter/copy/paste action for you.

 

I do have a basic function to help out when using cell colours, if it's any help to get you started. Add this code in to a module associated with the workbook, then you can use the function inside a worksheet just like you would any other Excel formula. It simply returns the integer associated with the selected cell's background colour.

 

Public Function cell_colour(Cell_Check As Range) As Long
   cell_colour = Cell_Check.Interior.Color
End Function

 

Let me know if you want more help with this and I'll see what I can do