Macro for spreadsheets with different number of rows and a constant number of columns

Hello,

I do not have experience with VBA that's why I'm asking for your help with the following macro. I work on a daily basis with spreadsheets in excel. The number of columns is the same, but every single spreadsheet has a different number of rows. I recorded this macro in a table with 1196 rows and I would like to use this macro also in other tables with a different number of rows. I would be more than grateful if you could help me with this issue. Thank you.

Sub City2()
'
' City2 Macro
'

'
Selection.Copy
Cells.Replace What:="POMPANO", Replacement:="Pompano Beach", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1195").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="COCOCRK", Replacement:="Coconut Creek", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1193").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="MARGATE", Replacement:="Margate", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1192").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="OAKPARK", Replacement:="Oakland Park", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1190").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="DEERFLD", Replacement:="Deerfield Beach", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1187").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="TAMARAC", Replacement:="Tamarac", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1175").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="NLAUDER", Replacement:="North Lauderdale", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1151").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Cells.Replace What:="LAUDLAKE", Replacement:="Lauderdale Lakes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1036").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="CORALSPR", Replacement:="Coral Springs", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B963").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="FORTLAUD", Replacement:="Fort Lauderdale", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B897").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="SUNRISE", Replacement:="Sunrise", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B866").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="LAUDHILL", Replacement:="Lauderhill", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B710").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="LHPOINT", Replacement:="Lighthouse Point", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B680").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="LAUDBSEA", Replacement:="Lauderdale by the Sea", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Range("B359").Select
Columns("B:B").ColumnWidth = 20.86
Range("B290").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Replace What:="PARKLAND", Replacement:="Parkland", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B177").Select
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Cells.Replace What:="SEARANCH", Replacement:="Sea Ranch Lakes", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B17").Select
Selection.Copy
Cells.Replace What:="HILLSBRO", Replacement:="Hillsboro Beach", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B1").Select
End Sub

Some additional information is needed.
From you code we can see the relation between "Range("B1195").Select" and "1196 rows" but what about others range selection.
To help seriously a file will be welcome

Hello,

Please find attached to this message the database. You will see that if you run the macro, the abbreviations from the column B are going to be replaced with the full name of the city. I have also other macros for this database which help me to replace the majority of the abbreviations. It would be awesome if you could tell me the code that I have to insert in the macro and where, because I would like to adapt the other macros too.

Thank you!

MORACH,
The macro don't work correctly because there is some selection done which fail.
The need for column B is to replace abbreviations by full name, there is some more flexible way to do instead to replace cells by cells:
use formula (inside the code) or correspondance table
Send a table to do the link between abbreviations and full name, something like:
Column A Column B
POMPANO Pompano Beach

Here's a table containing all the abbreviations that I use in my spreadsheets.

This is a link to the final version of the database.

https://www.dropbox.com/s/qhxxkv4t4z...ngleHouse.xlsm

There are many steps to follow until I obtain this version of the database, as you will see in the additional sheets. That's why it will simplify a lot my work a macro which can help me skip the "find & replace" the abbreviations from the initial database. I hope you can help me out with this issue.

Thank you very much.

Hi,

Thank you for your help. I tested the macro and it works. I saved the macros for the other tables. I was wondering if you could show me how to define a range of more columns in the macro, because for example for the equipment I have more than 1 column, and if I run this macro it will replace only the abreviations from the first column. I tried to apply it for the F2:S1925, but it didn't work. I'm not sure how to do it. Maybe when you have time you could take a look and try to figure it out. Please find attached the file.

Thanks again for your help

See here the macro prepared for Equipment
The range for column F to S has to prepared in :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The macro is working great! I really appreciate your help

Thank you.

Good news,
I don't know if you noticed that when there is no data found in the correspondance table the cells are painted in Red.
Here after the macro without this option

Please Login or Register  to view this content.

Thanks a lot, I paid more attention to the abbreviations and the to the lines to make sure that the lines do not get scrambled or something like that. I noticed that where I have OTHER or NONE, after I run the macro those words are replaced with 0 instead of blank space. Hm... I don't understand why.

"those words are replaced with 0 instead of blank space." that is because the corresponding value to OTHER or NONE is an empty cell: put a space